/*
 AMENDMENTS:
 DATE          	NAME        VERSION     DESCRIPTION
--------       	-----       -------     -----------
 30/10/2012    	MKANG       1.0.0       1.Create New.
*/
package com.mbbmap.app.home;

import java.io.UnsupportedEncodingException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;
import java.util.StringTokenizer;

import org.apache.commons.lang.time.DateFormatUtils;

import com.mbbmap.app.EbppException;
import com.mbbmap.app.dao.LogonUserDao;
import com.mbbmap.app.dao.UserDao;
import com.mbbmap.app.exception.LogonException;
import com.mbbmap.app.exception.UniqueKeyViolationException;
import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;
import com.mbbmap.app.manager.SystemLogManager;
import com.mbbmap.security.dao.SecUserDao;
import com.mbbmap.util.Constants;
import com.mbbmap.util.DateUtil;
import com.mbbmap.util.EnhancedException;
import com.mbbmap.util.MD5Helper;

public class SecUsersHome {
	private static SecUsersHome instance = null;

	/**Returns the single instance, creating one if it's the
	* first time this method is called.
	* 
	*
	* @return IslamicContractHome. The single instance.
	*/
	public static SecUsersHome getInstance() {
		//thread-safe implementation
		if (instance == null) {
			synchronized (SecUsersHome.class) {
				if (instance == null) {
					instance = new SecUsersHome();
				}
			}
		}
		return instance;
	}

	
	private SecUsersHome() {
		super();
	}
	
	
	/**Login
	 * @param organization organization id
	 * @param userId login user id
	 * @param password login password
	 * @param loginIpAddress login IP address
	 * @param paramProp Parameter Properties
	 * @return
	 * @throws LogonException
	 */
	public SecUserDao login (String organization, String userId, String password, String loginIpAddress) throws LogonException {
		int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
		DBConnectionManager connMgr;
		SecUserDao scUser = new SecUserDao();
		String connectionPool;
		SystemLogManager sysLogMgr;

		connectionPool = ConfigManager.getInstance().getConnectionPool();
		connMgr = DBConnectionManager.getInstance();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		sysLogMgr = SystemLogManager.getInstance();
		Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

		PreparedStatement stmt = null;
		ResultSet rs= null;
		String strPwd = "";
		boolean bfound = false;
		try {
			if (conn == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation("TransChangeHome_verifyApprover_001 [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			}else{
				String strSQL="";
				strSQL="SELECT LOGIN_ID,PASSWORD,ROLE_CODE,to_char(last_login_timestamp,'dd/mm/yyyy hh24:mi'),name, email, country_code " +
						" FROM TBLUSER WHERE MER_NAME='"+organization+"' AND LOGIN_ID='"+userId+"'";
				System.out.println("myDesk SecUsersHome.java strSQL="+strSQL);
				stmt = conn.prepareStatement(strSQL);
				rs = stmt.executeQuery();
				String strID="";
				String strRole="";

				if (rs.next()){
					strID=rs.getString(1);
					strPwd = rs.getString(2);
					strRole=rs.getString(3);
					scUser.set(strID,strRole,"A", "0");
					scUser.setLastLoginDateTime(rs.getString(4));
					scUser.setUserName(rs.getString(5));
					scUser.setEmail(rs.getString("email"));
					scUser.setRegion(rs.getString("country_code"));
					scUser.setCountryCode(rs.getString("country_code"));
				System.out.println("myDesk SecUsersHome.java strPwd="+strPwd);
				System.out.println("myDesk SecUsersHome.java MD5Helper.hash(password)="+MD5Helper.hash(password));
					if(strPwd.equals(MD5Helper.hash(password))){
						bfound=true;
					}
//					if(strPwd.equals(password)){
//						bfound=true;
//					}
				}
				System.out.println("myDesk SecUsersHome.java LOGONID="+scUser.getLogonId());
				stmt.close();
				rs.close();
				stmt=null;
				rs=null;
			} //if (conn == null)
			
			if(!bfound){
				scUser=null;
			}

		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("TransChangeHome.verifyApprover_002 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("TransChangeHome.verifyApprover_003 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e1) {}
			}
			connMgr.freeConnection(connectionPool, conn);
		}
		return scUser;
	
	}//end of login

	/**Get user
	 * 
	 * @param merchant
	 * @param logonUserRole
	 * @return ArrayList of UserDao
	 */
	
	
	/**
	 * Get list of users that the logonUserRole can see and users from child agencies only
	 * @param merchant
	 * @param logonUserRole
	 * @return ArrayList of UserDao
	 */

	public ArrayList getUsers (String merchant, String logonUserRoleId, String agencyCode, String countryCode, int maxBadPwdAttempts) {
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

        ArrayList users = new ArrayList();
        ArrayList roleIds = new ArrayList();
        ArrayList agencyIds = new ArrayList();
        System.out.println("Start getUsers process..");
        // get the child roles
        String selectSql = "select child_id from " +  Constants.DB_TBL_TREE +
        	" where mer_name = '"+merchant+"' AND parent_id = '"+logonUserRoleId+"' AND tree_type ='" + Constants.TREE_TYPE_ACCESS + "'";
        
        
        // get the child agencies
        String selectSql2 = "select child_id from " +  Constants.DB_TBL_TREE +
    	" where mer_name = '"+merchant+"' AND parent_id = '"+logonUserRoleId+"' AND tree_type ='" + Constants.TREE_TYPE_AGENCY + "'";
        
        
        StringBuffer sbSelect = new StringBuffer(
                "SELECT A.ID, A.LOGIN_ID, A.NAME, A.AGENCY_CODE, A.ROLE_CODE, A.CREATE_STATUS, A.ENABLED, A.LOCKED, A.BAD_PWD_NO_ATTEMPTS, A.REMARKS, B.ROLE_NAME FROM "
                        + Constants.DB_TBL_USER + " A, " + Constants.DB_TBL_ACCESSROLE + " B WHERE A.MER_NAME = ?  AND A.ROLE_CODE = B.ROLE_CODE ");
        
        
		//System.out.println("***************************"+countryCode);
    	//if (!logonUserRoleId.equals(Constants.DB_USER_ROLE_SUPER) && !logonUserRoleId.equals(Constants.DB_USER_ROLE_MSA)) {
		//	sbSelect.append(" AND A.COUNTRY_CODE='"+countryCode+"' ");
		//}
    	
    	System.out.println("merchant is "+merchant);
    	System.out.println("logonUserRoleId is "+logonUserRoleId);
    	System.out.println("agencyCode is "+agencyCode);

        try {
            connection = dbh.getDBConnection();
            PropertyHome ph = PropertyHome.getInstance();

            // Get the role ids that logon user role can see
            pstmtQuery =  connection.prepareStatement(selectSql);
            //pstmtQuery.setString(1, merchant);
            //pstmtQuery.setString(2, logonUserRoleId);
            System.out.println("SQL statement selectSql = "+selectSql);
            
            ResultSet rs = pstmtQuery.executeQuery();
            while (rs.next()) {
                roleIds.add(rs.getString(1));
            }
			pstmtQuery.close();
            rs.close();
			pstmtQuery=null;
            rs = null;

            // Get the child agency IDs that logon user can see
            pstmtQuery =  connection.prepareStatement(selectSql2);
            //pstmtQuery.setString(1, merchant);
            //pstmtQuery.setString(2, agencyCode);
            System.out.println("SQL statement selectSql2 = "+selectSql2);
            
            rs = pstmtQuery.executeQuery();
            while (rs.next()) {
                agencyIds.add(rs.getString(1));
            }
			pstmtQuery.close();
            rs.close();
			pstmtQuery=null;
            rs = null;

            // Get the users with the chid role id
            if (roleIds.size() > 0) {
	            sbSelect.append(" AND ( ");
	            for (int i=0; i < roleIds.size(); i++) {
	                sbSelect.append(" A.ROLE_CODE = '" + roleIds.get(i) + "' OR " );
	            }
	            sbSelect.delete(sbSelect.length()-3, sbSelect.length()); // remove the last OR
	            sbSelect.append(" )");
            }

            // Get the users with child agency id
            if (agencyIds.size() > 0) {
	            sbSelect.append(" AND ( ");
	            for (int i=0; i < agencyIds.size(); i++) {
	                sbSelect.append(" A.AGENCY_CODE = '" + agencyIds.get(i) + "' OR " );
	            }
	            sbSelect.delete(sbSelect.length()-3, sbSelect.length()); // remove the last OR
	            sbSelect.append(" )");
            }

            //System.out.println("SecUsersHome.getUsers() : sbSelect=" + sbSelect);

            pstmtQuery =  connection.prepareStatement(sbSelect.toString());
            pstmtQuery.setString(1, merchant);
            System.out.println("SQL statement sbSelect = "+sbSelect);
            
            rs = pstmtQuery.executeQuery();
            while (rs.next()) {
				UserDao user = new UserDao();
				user.setUserDBId(rs.getLong(1));
				user.setUserLogin(rs.getString(2));
				user.setUserName(rs.getString(3));
				user.setAgencyCode(rs.getString(4));
				user.setUserRole(rs.getString(5));
				user.setCreateStatus(rs.getString(6));
				user.setUserEnabled(rs.getString(7));

				if (!rs.getString(8).equals(Constants.DB_NO)) {
					user.setUserLocked(true);
				} else {
					int noPwdAttempts = rs.getInt(9);
					if (noPwdAttempts >= maxBadPwdAttempts) {
						user.setUserLocked(true);
					} else {
						user.setUserLocked(false);
					}
				}

				user.setRemark(rs.getString(10));
				user.setRoleName(rs.getString(11));

				// get email and contact No
				//user.setEmail(ph.getProperty(Long.toString(user.getUserDBId()), user, UserDao.PROP_EMAIL));
				//user.setHandPhoneNo(ph.getProperty(Long.toString(user.getUserDBId()), user, UserDao.PROP_HANDPHONE_NO));

				if(logonUserRoleId.equals(Constants.DB_USER_ROLE_BRSA) && !agencyCode.equals(rs.getString(4))){
				}else{
					users.add(user);
				}
            }
			pstmtQuery.close();
            rs.close();

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.getUsers() SQL execution : sql1=" + selectSql + "\n sql2=" +sbSelect + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

	    return users;

	}

	
	/**
	 * @param merchant merchant id
	 * @param userId login user id
	 * @param password login password
	 * @param loginIpAddress login IP address
	 * @param paramProp Parameter Properties
	 * @return
	 * @throws LogonException
	 */
	public SecUserDao login (String merchant, String userId, String password, String loginIpAddress, Properties paramProp) throws LogonException {

	    boolean success = true;
        Connection connection = null;
        PreparedStatement pstmtUpdate = null, pstmtQuery = null;
        int noOfRecords=0;
        LogonUserDao logonUser = new LogonUserDao();
        SecUserDao secUserDao = new SecUserDao();
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
        
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

        // get parameter properties
        String pwdExpiryDays = paramProp.getProperty(ParamHome.SEC_PW_EXPIRY_DAY);
        String badPwdAttempts = paramProp.getProperty(ParamHome.SEC_BAD_PW_ATTEMPTS);
        String changePwdFirstLogon = paramProp.getProperty(ParamHome.SEC_CHG_PW_FIR_LOGON);
        String improperLogoutTimeOutMin = paramProp.getProperty(ParamHome.SEC_LOGOUT_TIME);
		sysLogMgr.logMsg("SecUsersHome.login() Step 03A userId : [" + userId + "]-loginIpAddress=["+loginIpAddress+"]-Enter into Login Function");

        try {
			connection = dbh.getDBConnection();
      		connection.setAutoCommit(false);
		sysLogMgr.logMsg("SecUsersHome.login() Step 03B merchant: ["+merchant+"] [userId : [" + userId + "]-loginIpAddress=["+loginIpAddress+"]-Check if User exists");
			// check that the user id exist
			if (! userExist (merchant, userId, connection)) {
				throw new LogonException(LogonException.INVALID_USER_ID);
			}

		sysLogMgr.logMsg("SecUsersHome.login() Step 04 userId : [" + userId + "]-loginIpAddress=["+loginIpAddress+"]-Check if BadPwdAttempts more than 3");
			// check no. of bad pwd attempts
			if (getNoOfBadPwdAttempts (merchant, userId, connection) >= Integer.parseInt(badPwdAttempts) ){
				throw new LogonException (LogonException.TOO_MANY_BAD_ATTEMPTS);
			}
			

			// check user logon id and password
//			String selectSql = "SELECT A.NAME, A.AGENCY_CODE, A.LAST_LOGIN_TIMESTAMP, A.ROLE_CODE, " +
//					"A.BAD_PWD_NO_ATTEMPTS, A.ENABLED, A.LOCKED, A.RESET_PWD_FLAG, A.LAST_PWD_CHANGED, A.NO_ACCESS_DATE, A.CREATE_STATUS, " +
//					"B.ROLE_NAME, C.COUNTRY_CODE FROM " + Constants.DB_TBL_USER + " A, " +
//					Constants.DB_TBL_ACCESSROLE + " B, " + Constants.DB_TBL_AGENCY + " C " +
//					" WHERE A.MER_NAME = ? AND A.LOGIN_ID= ? AND A.ROLE_CODE = B.ROLE_CODE " +
//					" AND A.COUNTRY_CODE = C.COUNTRY_CODE AND A.AGENCY_CODE = C.AGENCY_CODE AND A.PASSWORD = ? ";
			
			// check user logon id and password
			String selectSql = "SELECT A.NAME, A.LAST_LOGIN_TIMESTAMP, A.ROLE_CODE, " +
					"A.BAD_PWD_NO_ATTEMPTS, A.ENABLED, A.LOCKED, A.RESET_PWD_FLAG, A.LAST_PWD_CHANGED, A.NO_ACCESS_DATE, A.CREATE_STATUS, " +
					"B.ROLE_NAME, A.EMAIL, A.COUNTRY_CODE FROM " + Constants.DB_TBL_USER + " A, " +
					Constants.DB_TBL_ACCESSROLE + " B  " +
					" WHERE A.MER_NAME = ? AND A.LOGIN_ID= ? AND A.ROLE_CODE = B.ROLE_CODE " +
					" AND A.PASSWORD = ? ";

			// update user table upon login
			String updateSql = "UPDATE " + Constants.DB_TBL_USER + " SET LAST_LOGIN_TIMESTAMP = ?, " +
					"LAST_LOGIN_IP = ?,  BAD_PWD_NO_ATTEMPTS = 0, LOCKED = '" + Constants.DB_LOGIN_LOCKED +
					"' WHERE LOGIN_ID = ? AND MER_NAME = ? ";


		sysLogMgr.logMsg("SecUsersHome.login() Step 05A userId : [" + userId + "]-loginIpAddress=["+loginIpAddress+"]-Select user details from tbluser -selectSql=[" + selectSql+ "]");
            // check user id and password
            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, userId);
//            pstmtQuery.setString(3, password);
            pstmtQuery.setString(3, MD5Helper.hash(password));

            //Check sql query and user info.
            System.out.println("SQL : " + selectSql);
            System.out.println("userId=[" + userId + "],  password= [" + MD5Helper.hash(password) + "] merchant = [" + merchant + "]");

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
            	
                logonUser.setLoginId(userId);
                logonUser.setName(rs.getString(1));
//                logonUser.setAgencyCode(rs.getString(2));
                logonUser.setLastLogin(rs.getTimestamp(2));
                logonUser.setRoleCode(rs.getString(3));
                logonUser.setNoOfBadPwdAttempts(rs.getInt(4));
                logonUser.setEnabled(rs.getString(5));
                logonUser.setLocked(rs.getString(6));
                logonUser.setResetPwd(rs.getString(7));
                logonUser.setLastPwdChanged(rs.getDate(8));
                logonUser.setNoAccessDate(rs.getDate(9));
                logonUser.setCreateStatus(rs.getString(10));

//                secUserDao.set(userId, logonUser.getRoleCode(), rs.getString(12), logonUser.getAgencyCode());
                secUserDao.set(userId, logonUser.getRoleCode(), rs.getString(11), "");
                secUserDao.setUserName(logonUser.getName());
                secUserDao.setCountryCode(rs.getString("COUNTRY_CODE"));
                secUserDao.setEmail(rs.getString("EMAIL"));
                secUserDao.setRegion(rs.getString("COUNTRY_CODE"));
               
				pstmtQuery.close();
                rs.close();

            } else {
				pstmtQuery.close();
                rs.close();

				sysLogMgr.logMsg("SecUsersHome.login() Step 05B userId : [" + userId + "]-loginIpAddress=["+loginIpAddress+"]-User Password Invalid ! Increase BadPwdAttempts");
                // password not match, increase the bad password attempts
                if (! increaseNoOfBadPwdAttempts(merchant, userId, connection))
                    throw new EbppException ("Fail to update no. of bad password attempts");
                throw new LogonException(LogonException.INVALID_USER_PWD);
            }
			//pstmtQuery.close();
            java.util.Date now = new java.util.Date();
			System.out.println("CPA SecUsersHome.java Login (Step05B_1)");
            // check if the user is currently logged in (has not logout) and locked from other login attempts
            if (logonUser.getLocked().equals(Constants.DB_LOGGED_IN)) {
                // if it is improper logout then compare to timeout value whether to allow proceed
                //java.util.Date lastAccessed = SysLogHome.getInstance().getDateLastAccess(userId);
				java.util.Date lastAccessed = logonUser.getLastLogin();
				//System.out.println("SecUsersHome.java login() lastAccessed=" + lastAccessed);
				sysLogMgr.logMsg("SecUsersHome.login() Step 05D userId : [" + userId + "]-loginIpAddress=["+loginIpAddress+"]-lastAccessed! =" + lastAccessed);

                if (lastAccessed == null) {
                    throw new LogonException(LogonException.IMPROPER_LOGOUT);
                }
                System.out.println("SecUsersHome.java login() DateFormatUtils.format now=" + DateFormatUtils.format(now,"yyyyMMdd"));
                System.out.println("SecUsersHome.java login() DateFormatUtils.format lastAccessed=" + DateFormatUtils.format(lastAccessed,"yyyyMMdd"));
                if(DateFormatUtils.format(now,"yyyyMMdd").equals(DateFormatUtils.format(lastAccessed,"yyyyMMdd"))){
                //if (!DateUtils.isSameDay(now,lastAccessed)){
					//If not same day ignore
					int iNow = Integer.parseInt(DateFormatUtils.format(now,"HHmmss"));
					int iLast = Integer.parseInt(DateFormatUtils.format(lastAccessed,"HHmmss"));
					int iTim = iNow - iLast;
                System.out.println("SecUsersHome.java login() DateFormatUtils.format iNow=" + iNow);
                System.out.println("SecUsersHome.java login() DateFormatUtils.format iLast=" + iLast);
                System.out.println("SecUsersHome.java login() DateFormatUtils.format iTim=" + iTim);
                System.out.println("SecUsersHome.java login() DateFormatUtils.format Integer.parseInt(improperLogoutTimeOutMin)*60*1000)=" + Integer.parseInt(improperLogoutTimeOutMin)*60*1000);
					if (now.getTime() - lastAccessed.getTime() < (Integer.parseInt(improperLogoutTimeOutMin)*60*1000) ) {
						throw new LogonException(LogonException.IMPROPER_LOGOUT);
					}
				}
            }
            // check also if the user is locked by the system (i.e. due to user inactivity)
            else if (logonUser.getLocked().equals(Constants.DB_YES)) {
                throw new LogonException(LogonException.USER_LOCKED);
            }
			System.out.println("CPA SecUsersHome.java Login (Step05D_1)");

            // check that the no of badPwdAttempts has not exceeded
            if ( logonUser.getNoOfBadPwdAttempts() >= (Integer.parseInt(badPwdAttempts))) {
                throw new LogonException(LogonException.TOO_MANY_BAD_ATTEMPTS);
            }
			System.out.println("CPA SecUsersHome.java Login (Step05D_2)");

            // check if the user is enabled
            if (logonUser.getEnabled().equals(Constants.DB_FLAG_DORMANT)) {
                throw new LogonException(LogonException.INACTIVE_USER);
            }else if (!logonUser.getEnabled().equals(Constants.DB_YES)) {
                throw new LogonException(LogonException.USER_DISABLED);
            }
			System.out.println("CPA SecUsersHome.java Login (Step05D_3)");

            // check if the user create status is approved
            if (!logonUser.getCreateStatus().equals(Constants.DB_USER_CREATE_APPROVE)) {
                throw new LogonException(LogonException.USER_STATUS_NOT_APPROVED);
            }
			System.out.println("CPA SecUsersHome.java Login (Step05D_4)");

            // check if the user need to change password if reset password flag is set to Y (i.e. after admin reset user password)
            if (logonUser.getResetPwd().equals(Constants.DB_YES)) {
                throw new LogonException(LogonException.CHANGE_PWD);
            }
			System.out.println("CPA SecUsersHome.java Login (Step05D_5 changePwdFirstLogon="+changePwdFirstLogon+")");
			System.out.println("CPA SecUsersHome.java Login (Step05D_5 logonUser.getLastLogin()="+logonUser.getLastLogin()+")");
			if(changePwdFirstLogon==null){
				changePwdFirstLogon="";
			}
            // check if this is the first logon and user need to change password
            if (changePwdFirstLogon.equals(Constants.DB_YES) && logonUser.getLastLogin() == null ) {
                throw new LogonException(LogonException.CHANGE_PWD_FIRST_LOGON);
            }
            
			System.out.println("CPA SecUsersHome.java Login (Step05D_6)");
			
            // check if the user's current password has expired
			System.out.println("Check password expired.");
			
            java.util.Date lastPwdChangedDate = logonUser.getLastPwdChanged();
			System.out.println("Check password logonUser.getLastPwdChanged()=."+lastPwdChangedDate);
			//if (!logonUser.getRoleCode().equals("ROOT")){
				if (lastPwdChangedDate !=null && DateUtil.getDaysBetween(lastPwdChangedDate, now) >=  Integer.parseInt(pwdExpiryDays)) {
					System.out.println("Check password lastPwdChangedDate=."+lastPwdChangedDate);
					System.out.println("The days in diff "+DateUtil.getDaysBetween(lastPwdChangedDate, now));
					System.out.println("The given expiry days given "+Integer.parseInt(pwdExpiryDays));
					throw new LogonException(LogonException.CHANGE_PWD_EXP);
				}
			//}
			// check that the user password expired using method userpasswordExpired.
				//if (userpasswordExpired (merchant, userId, pwdExpiryDays)) {
		        //throw new LogonException(LogonException.CHANGE_PWD_EXP);
		       //}
				
			System.out.println("CPA SecUsersHome.java Login (Step05D_7)");

            // check if user has no access today
            if (logonUser.getNoAccessDate() != null) {
                if(dateFormat.format(now).equals(dateFormat.format(logonUser.getNoAccessDate()))) {
                    throw new LogonException(LogonException.NO_ACCESS_DATE);
                }
            }
			System.out.println("CPA SecUsersHome.java Login (Step05D_8)");

	        //pstmtUpdate.close();
			sysLogMgr.logMsg("SecUsersHome.login() Step 05E userId : [" + userId + "]-loginIpAddress=["+loginIpAddress+"]-Login End");
			connection.commit();
			connection.setAutoCommit(true);

			if (success){
				pstmtUpdate =  connection.prepareStatement(updateSql);
				pstmtUpdate.setTimestamp(1, new java.sql.Timestamp(now.getTime()) );
				pstmtUpdate.setString(2, loginIpAddress);
				pstmtUpdate.setString(3, userId);
				pstmtUpdate.setString(4, merchant);
				int loginUpdate = pstmtUpdate.executeUpdate();
				pstmtUpdate.close();
				pstmtUpdate=null;
			}
	        //if (success && noOfRecords < 1 )
	        //    success = false;

        } catch (NoSuchAlgorithmException nsae) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.login() MD5 Hash error : " + "\n" + nsae);
            nsae.printStackTrace();
        } catch (UnsupportedEncodingException uee) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.login() MD5 Hash error : " + "\n" + uee);
            uee.printStackTrace();
        } catch (java.sql.SQLException sqle) {
            success  = false;
            //sysLogMgr.logMsg("Error in SecUsersHome.login() SQL execution,  selectSql: " + selectSql + "\n updateSql : " + updateSql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (NumberFormatException nfe) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.login(). Error in formatting String to int \n" + nfe);
            nfe.printStackTrace();
        } catch (EbppException ebppe) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.login() : \n" + ebppe);
            ebppe.printStackTrace();

        } finally {

			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null){
			    try {
			        connection.setAutoCommit(true); //***//
			    } catch (SQLException e1) {
			    }
			    dbh.freeDBConnection (connection);
			}
		}

        if (success)
            return secUserDao;
        else
            return null;
	}
	
	/**
	 * @param merchant merchant id
	 * @param userId login user id
	 * @param password login password
	 * @param loginIpAddress login IP address
	 * @param paramProp Parameter Properties
	 * @return
	 * @throws LogonException
	 */
	public int updLastActiveTimestamp (String merchant, String userId, String loginIpAddress) {

        Connection connection = null;
        PreparedStatement pstmtUpdate = null;
        int success = 0;

        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
        java.util.Date now = new java.util.Date();
        
        try {
			connection = dbh.getDBConnection();
      		connection.setAutoCommit(false);

			// update user table upon login
			String updateSql = "UPDATE " + Constants.DB_TBL_USER + " SET LAST_LOGIN_TIMESTAMP = ?, " +
					"LAST_LOGIN_IP = ? " +
					"WHERE LOGIN_ID = ? AND MER_NAME = ? ";

			pstmtUpdate =  connection.prepareStatement(updateSql);
			pstmtUpdate.setTimestamp(1, new java.sql.Timestamp(now.getTime()) );
			pstmtUpdate.setString(2, loginIpAddress);
			pstmtUpdate.setString(3, userId);
			pstmtUpdate.setString(4, merchant);
			success = pstmtUpdate.executeUpdate();

        } catch (java.sql.SQLException sqle) {
            //sysLogMgr.logMsg("Error in SecUsersHome.login() SQL execution,  selectSql: " + selectSql + "\n updateSql : " + updateSql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (NumberFormatException nfe) {
            sysLogMgr.logMsg("Error in SecUsersHome.login(). Error in formatting String to int \n" + nfe);
            nfe.printStackTrace();
        } finally {
			if (pstmtUpdate != null) {
				try {
					pstmtUpdate.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null){
			    try {
			    	connection.commit();
			        connection.setAutoCommit(true); //***//
			    } catch (SQLException e1) {
			    }
			    dbh.freeDBConnection (connection);
			}
		}
        return success;
	}

	
	/**
	 * @param merchant merchant id
	 * @param userId login user id
	 * @param password login password
	 * @param loginIpAddress login IP address
	 * @param paramProp Parameter Properties
	 * @return
	 * @throws LogonException
	 */
	public SecUserDao loginChangePwd (String merchant, String userId, String password, String newPwd, String loginIpAddress, Properties paramProp) throws LogonException {

	    boolean success = true;
        Connection connection = null;
        PreparedStatement pstmtUpdate = null, pstmtQuery = null;
        int noOfRecords=0;
        LogonUserDao logonUser = new LogonUserDao();
        SecUserDao secUserDao = new SecUserDao();
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
        long userDBId = 0;

        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
        PropertyHome propHome = PropertyHome.getInstance();

        // get parameter properties
        String pwdExpiryDays = paramProp.getProperty(ParamHome.SEC_PW_EXPIRY_DAY);
        String badPwdAttempts = paramProp.getProperty(ParamHome.SEC_BAD_PW_ATTEMPTS);
        String changePwdFirstLogon = paramProp.getProperty(ParamHome.SEC_CHG_PW_FIR_LOGON);
        String improperLogoutTimeOutMin = paramProp.getProperty(ParamHome.SEC_LOGOUT_TIME);
        String allowSameUserIdPwd = paramProp.getProperty(ParamHome.SEC_ALLOW_SAME_USER_ID_PW);

        // check that the user id exist
        if (! userExist (merchant, userId)) {
            throw new LogonException(LogonException.INVALID_USER_ID);
        }

        // check no. of bad pwd attempts
        if (getNoOfBadPwdAttempts (merchant, userId) >= Integer.parseInt(badPwdAttempts) ){
            throw new LogonException (LogonException.TOO_MANY_BAD_ATTEMPTS);
        }

        // Check whether user can have user ID same with password
        if (allowSameUserIdPwd.equals(Constants.DB_NO)) {
            if (userId.equals(newPwd)) {
                throw new LogonException(LogonException.USER_ID_PWD_SAME);
            }
        }
        
        // check user logon id and password
//        String selectSql = "SELECT A.ID, A.NAME, A.AGENCY_CODE, A.LAST_LOGIN_TIMESTAMP, A.ROLE_CODE, " +
//        		"A.BAD_PWD_NO_ATTEMPTS, A.ENABLED, A.LOCKED, A.RESET_PWD_FLAG, A.LAST_PWD_CHANGED, A.NO_ACCESS_DATE, A.CREATE_STATUS, " +
//        		"B.ROLE_NAME, C.COUNTRY_CODE FROM " + Constants.DB_TBL_USER + " A, " +
//        		Constants.DB_TBL_ACCESSROLE + " B, " + Constants.DB_TBL_AGENCY + " C " +
//        		" WHERE A.MER_NAME = ? AND A.LOGIN_ID= ? AND A.ROLE_CODE = B.ROLE_CODE " +
//        		" AND A.COUNTRY_CODE = C.COUNTRY_CODE AND A.AGENCY_CODE = C.AGENCY_CODE AND A.PASSWORD = ? ";
        
     // check user logon id and password
        String selectSql = "SELECT A.ID, A.NAME, A.LAST_LOGIN_TIMESTAMP, A.ROLE_CODE, " +
        		"A.BAD_PWD_NO_ATTEMPTS, A.ENABLED, A.LOCKED, A.RESET_PWD_FLAG, A.LAST_PWD_CHANGED, A.NO_ACCESS_DATE, A.CREATE_STATUS, " +
        		"B.ROLE_NAME FROM " + Constants.DB_TBL_USER + " A, " +
        		Constants.DB_TBL_ACCESSROLE + " B " +
        		" WHERE A.MER_NAME = ? AND A.LOGIN_ID= ? AND A.ROLE_CODE = B.ROLE_CODE " +
        		" AND A.PASSWORD = ? ";
        try {
			System.out.println("The user password is "+MD5Helper.hash(password));
		} catch (NoSuchAlgorithmException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        // update user table upon change password and login
        String updateSql = "";
        System.out.println("Check user logonid and password using SQL query statement "+selectSql);
        try {

            connection = dbh.getDBConnection();
      		connection.setAutoCommit(false);

            // check user id and password
            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, userId);
            pstmtQuery.setString(3, MD5Helper.hash(password));

            //System.out.println("SQL : " + selectSql);
            //System.out.println("userId=[" + userId + "],  password= [" + MD5Helper.hash(password) + "] merchant = [" + merchant + "]");

            // execute query
            System.out.println("Execute SQl query.");
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {

                logonUser.setLoginId(userId);
                userDBId = rs.getLong(1);
                System.out.println("The user db id is "+ userDBId);
                logonUser.setName(rs.getString(2));
                System.out.println("logonUser.setName(rs.getString(2)) :"+rs.getString(2));
//              logonUser.setAgencyCode(rs.getString(3));
                logonUser.setLastLogin(rs.getTimestamp(3));
                System.out.println("logonUser.setLastLogin(rs.getTimestamp(3)) :"+rs.getTimestamp(3));
				logonUser.setRoleCode(rs.getString(4));
				
                logonUser.setNoOfBadPwdAttempts(rs.getInt(5));
                logonUser.setEnabled(rs.getString(6));
                logonUser.setLocked(rs.getString(7));
                logonUser.setResetPwd(rs.getString(8));
                logonUser.setLastPwdChanged(rs.getDate(9));
                logonUser.setNoAccessDate(rs.getDate(10));
                logonUser.setCreateStatus(rs.getString(11));

                secUserDao.set(userId, logonUser.getRoleCode(), rs.getString(12), logonUser.getAgencyCode());
                secUserDao.setUserName(logonUser.getName());
//              secUserDao.setCountryCode(rs.getString(14));
				pstmtQuery.close();
                rs.close();
				pstmtQuery=null;

            } else {
				pstmtQuery.close();
                rs.close();
				pstmtQuery=null;

                // password not match, increase the bad password attempts
                System.out.println("Increase bad password attempts.");
                if (! increaseNoOfBadPwdAttempts(merchant, userId, connection))
                    throw new EbppException ("Fail to update no. of bad password attempts");
                throw new LogonException(LogonException.INVALID_USER_PWD);
            }

            java.util.Date now = new java.util.Date();
/*
            // check if the user is currently logged in (has not logout) and locked from other login attempts
            System.out.println("Check user is logged in.");
            if (logonUser.getLocked().equals(Constants.DB_LOGGED_IN)) {
                // if it is improper logout then compare to timeout value whether to allow proceed
                //java.util.Date lastAccessed = SysLogHome.getInstance().getDateLastAccess(userId);
                java.util.Date lastAccessed = logonUser.getLastLogin();
                if (lastAccessed == null) {
                    throw new LogonException(LogonException.IMPROPER_LOGOUT);
                }
                if(DateFormatUtils.format(now,"yyyyMMdd").equals(DateFormatUtils.format(lastAccessed,"yyyyMMdd"))){
                //if (!DateUtils.isSameDay(now,lastAccessed)){
					//If not same day ignore
					int iNow = Integer.parseInt(DateFormatUtils.format(now,"HHmmss"));
					int iLast = Integer.parseInt(DateFormatUtils.format(lastAccessed,"HHmmss"));
					int iTim = iNow - iLast;
                //System.out.println("SecUsersHome.java login() DateFormatUtils.format iNow=" + iNow);
                //System.out.println("SecUsersHome.java login() DateFormatUtils.format iLast=" + iLast);
                //System.out.println("SecUsersHome.java login() DateFormatUtils.format iTim=" + iTim);
                //System.out.println("SecUsersHome.java login() DateFormatUtils.format Integer.parseInt(improperLogoutTimeOutMin)*60*1000)=" + Integer.parseInt(improperLogoutTimeOutMin)*60*1000);
					if (now.getTime() - lastAccessed.getTime() < (Integer.parseInt(improperLogoutTimeOutMin)*60*1000) ) {
						throw new LogonException(LogonException.IMPROPER_LOGOUT);
					}
				}
            }
            // check also if the user is locked by the system (i.e. due to user inactivity)
            else 
*/			
			if (logonUser.getLocked().equals(Constants.DB_YES)) {
                throw new LogonException(LogonException.USER_LOCKED);
            }

            // check that the no of badPwdAttempts has not exceeded
            System.out.println("Check the number of bad password attempts.");
            if ( logonUser.getNoOfBadPwdAttempts() >= (Integer.parseInt(badPwdAttempts))) {
                throw new LogonException(LogonException.TOO_MANY_BAD_ATTEMPTS);
            }

            // check if the user is enabled
            System.out.println("Check user enabled.");
            if (logonUser.getEnabled().equals(Constants.DB_FLAG_DORMANT)) {
                throw new LogonException(LogonException.INACTIVE_USER);
            }else if (!logonUser.getEnabled().equals(Constants.DB_YES)) {
                throw new LogonException(LogonException.USER_DISABLED);
            }

            // check if the user create status is approved
            if (!logonUser.getCreateStatus().equals(Constants.DB_USER_CREATE_APPROVE)) {
                throw new LogonException(LogonException.USER_STATUS_NOT_APPROVED);
            }

            // check if user has no access today
            if (logonUser.getNoAccessDate() != null) {
                if(dateFormat.format(now).equals(dateFormat.format(logonUser.getNoAccessDate()))) {
                    throw new LogonException(LogonException.NO_ACCESS_DATE);
                }
            }


			// Get the history password setting
            int currTotalHistPwd =0;
            int totalHistoryPwd = 0;
  			int count = 0;
            ArrayList pwdList = new ArrayList();

            System.out.println("``````````loginChangePwd user Db ID is "+Long.toString(userDBId));
            String strCurrTotalHistPwd = propHome.getProperty(Long.toString(userDBId), secUserDao, Constants.CURRENT_HISTORY_PASSWORD_STORED);
            System.out.println("``````````strCurrTotalHistPwd="+strCurrTotalHistPwd);
            if( strCurrTotalHistPwd == null || strCurrTotalHistPwd.equals("")) {
	      		currTotalHistPwd = 0;
	      	} else {
	      		currTotalHistPwd = Integer.parseInt(strCurrTotalHistPwd);
	       }

  			// Get total history passwords to track
  			String totalPwdHist = ParamHome.getInstance().getParamValue(merchant, ParamHome.PARAM_TYPE_TECH, ParamHome.TECH_PARAM_TOTAL_PASSWORD_HIST);

  			if (totalPwdHist == null || totalPwdHist.equals("")) {
                totalHistoryPwd = 0;
            } else {
                totalHistoryPwd = Integer.parseInt(totalPwdHist);
            }
  			//System.out.println("``````````totalHistoryPwd="+totalHistoryPwd);
  			// new password and old password must not be the same
  			if (password.equals(newPwd)) {
  				System.out.println("The new password is the same as the old password.");
  			    throw new LogonException(LogonException.NEW_PWD_EXIST);
  			}

  			// Store history password in arraylist
            if (currTotalHistPwd != 0) {
                for (int i = 1; i <= currTotalHistPwd; i++) {
                    String propertyPassword = "";

                    propertyPassword = propHome.getProperty(Long.toString(userDBId), secUserDao,
                            Constants.HISTORY_PASSWORD + "_" + Integer.toString(i));

                    pwdList.add(propertyPassword);

                    if (propertyPassword.equals(MD5Helper.hash(newPwd))) {
                        count++;
                    }
                }
            }

            if (count > 0) {
            	System.out.println("The new password exist in history");
                throw new LogonException(LogonException.NEW_PWD_EXIST);
            }

			connection.commit();
			connection.setAutoCommit(true);
            // Now change the password and update the table
            // update user table upon login
            connection = dbh.getDBConnection();
      		connection.setAutoCommit(false);

            updateSql = "UPDATE " + Constants.DB_TBL_USER + " SET PASSWORD= ?, LAST_LOGIN_TIMESTAMP = ?, " +
    		"LAST_LOGIN_IP = ?,  BAD_PWD_NO_ATTEMPTS = 0, " +
    		"RESET_PWD_FLAG = '" + Constants.DB_NO + "', LAST_PWD_CHANGED= ? WHERE LOGIN_ID = ? AND MER_NAME = ? ";

           // Now update the user record
            pstmtUpdate = connection.prepareStatement(updateSql);
            pstmtUpdate.setString(1, MD5Helper.hash(newPwd));
            pstmtUpdate.setTimestamp(2, new Timestamp(now.getTime()));
            pstmtUpdate.setString(3, loginIpAddress);
            pstmtUpdate.setTimestamp(4, new Timestamp(now.getTime()));
            pstmtUpdate.setString(5, userId);
            pstmtUpdate.setString(6, merchant);

	        // execute the insert statement;
	        noOfRecords = pstmtUpdate.executeUpdate();
			pstmtUpdate.close();
			pstmtUpdate=null;

	        if (success && noOfRecords < 1 )
	            success = false;

	        if (success) {
	            //System.out.println("````````````Update Success !!");
	            // Update the history password property
  				if (secUserDao.getGroupCode() != null && !secUserDao.getGroupCode().equals("")){
  		            //System.out.println("````````````Update Success 2 !!");
  					//set password property
  					if(!pwdList.isEmpty())
  					{
  					    if(currTotalHistPwd < totalHistoryPwd)
  						{
  					        propHome.setProperty(Long.toString(userDBId), Integer.toString(currTotalHistPwd+1),
  							        Constants.CURRENT_HISTORY_PASSWORD_STORED, secUserDao);
  							propHome.setProperty(Long.toString(userDBId), MD5Helper.hash(newPwd),
  							        Constants.HISTORY_PASSWORD + "_" + Integer.toString(currTotalHistPwd+1), secUserDao);
  						}
  						else if(currTotalHistPwd == totalHistoryPwd)
  						{
  						    if(totalHistoryPwd > 0)
  							{
  								if(totalHistoryPwd > 1)
  								{
  								    for(int j=1; j <pwdList.size(); j++)
  									{
  										propHome.setProperty(Long.toString(userDBId), (String)pwdList.get(j),
  											     Constants.HISTORY_PASSWORD + "_" + Integer.toString(j), secUserDao);

  									}
  								}

  								propHome.setProperty(Long.toString(userDBId), Integer.toString(currTotalHistPwd),
  	  							        Constants.CURRENT_HISTORY_PASSWORD_STORED, secUserDao);
  	  							propHome.setProperty(Long.toString(userDBId), MD5Helper.hash(newPwd),
  	  							        Constants.HISTORY_PASSWORD + "_" + Integer.toString(currTotalHistPwd), secUserDao);

  							}
  							else
  							{
  							    propHome.removeProperty(Long.toString(userDBId), Constants.CURRENT_HISTORY_PASSWORD_STORED, secUserDao);
  							}
  						}
  						else if(currTotalHistPwd > totalHistoryPwd)
  						{
  						    for(int a = 1; a<=currTotalHistPwd; a++)
  							{
  							  propHome.removeProperty(Long.toString(userDBId), Constants.HISTORY_PASSWORD + "_" + Integer.toString(a), secUserDao);
  							}

  							if(totalHistoryPwd > 0)
  							{
  							    int k = (currTotalHistPwd + 1) - totalHistoryPwd;
  								int i =1;

  								for(int j=k; j <pwdList.size(); j++)
  								{
  	  	  							propHome.setProperty(Long.toString(userDBId), (String)pwdList.get(j),
  	  	  							  Constants.HISTORY_PASSWORD + "_" + Integer.toString(i), secUserDao);
  									i++;
  								}

  								propHome.setProperty(Long.toString(userDBId), Integer.toString(totalHistoryPwd),
  								        Constants.CURRENT_HISTORY_PASSWORD_STORED, secUserDao);
  	  							propHome.setProperty(Long.toString(userDBId), MD5Helper.hash(newPwd),
  	  							    Constants.HISTORY_PASSWORD + "_" + Integer.toString(totalHistoryPwd), secUserDao);
  							}
  							else
  							{
  							    propHome.removeProperty(Long.toString(userDBId), Constants.CURRENT_HISTORY_PASSWORD_STORED, secUserDao);
  							}
  						}
  						else
  						{
  						    propHome.setProperty(Long.toString(userDBId), "1",
								        Constants.CURRENT_HISTORY_PASSWORD_STORED, secUserDao);
  	  							propHome.setProperty(Long.toString(userDBId), MD5Helper.hash(newPwd),
  	  							    Constants.HISTORY_PASSWORD + "_1", secUserDao);
  						}
  					}
  					else
  					{
  					    if(totalHistoryPwd > 1)
  						{
  					        propHome.setProperty(Long.toString(userDBId), "2",
  								    Constants.CURRENT_HISTORY_PASSWORD_STORED, secUserDao);
  	  						propHome.setProperty(Long.toString(userDBId), MD5Helper.hash(password),
  	  							    Constants.HISTORY_PASSWORD + "_1", secUserDao);
  	  						propHome.setProperty(Long.toString(userDBId), MD5Helper.hash(newPwd),
  	  							    Constants.HISTORY_PASSWORD + "_2", secUserDao);

  						}
  					    else if(totalHistoryPwd == 1)
  						{
  					        propHome.setProperty(Long.toString(userDBId), "1",
  								    Constants.CURRENT_HISTORY_PASSWORD_STORED, secUserDao);
  	  						propHome.setProperty(Long.toString(userDBId), MD5Helper.hash(newPwd),
  	  							    Constants.HISTORY_PASSWORD + "_1", secUserDao);

  						}
  					}
  				} // role code exist
	        } // if (success)
			connection.commit();
			connection.setAutoCommit(true);

        } catch (NoSuchAlgorithmException nsae) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.loginChangePwd() MD5 Hash error : " + "\n" + nsae);
            nsae.printStackTrace();
        } catch (UnsupportedEncodingException uee) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.loginChangePwd() MD5 Hash error : " + "\n" + uee);
            uee.printStackTrace();
        } catch (java.sql.SQLException sqle) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.loginChangePwd() SQL execution,  selectSql: " + selectSql + "\n updateSql : " + updateSql + "\n" + sqle);
            sqle.printStackTrace();
        } catch (NumberFormatException nfe) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.loginChangePwd(). Error in formatting String to int / long \n" + nfe);
            nfe.printStackTrace();
        } catch (EbppException ebppe) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.loginChangePwd() : \n" + ebppe);
            ebppe.printStackTrace();

        } finally {
			if (pstmtUpdate != null) {
				try {
				    pstmtUpdate.close();
				} catch (SQLException e1) {
				}
			}
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null){
				try {
						connection.setAutoCommit(true); //***//
					} catch (SQLException e1) {
					}
					dbh.freeDBConnection (connection);
			}
		}

        if (success)
            return secUserDao;
        else
            return null;
	}
	


	/**Check for user existence before invoking login
	 * 
	 * @param merchant
	 * @param userId
	 * @return
	 */
	public boolean userExist (String merchant, String userId, Connection connection) {
	    boolean exist = false;
        //Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        //DBAccessHome dbh = DBAccessHome.getInstance();

        // check user logon id
        String selectSql = "SELECT LOGIN_ID FROM " + Constants.DB_TBL_USER + " WHERE MER_NAME = ? AND LOGIN_ID= ?  ";
        //String selectSql = "SELECT LOGIN_ID FROM TBLUSER WHERE MER_NAME = 'MBB_MAP' AND LOGIN_ID= 'mbbadmin' ";
        //String selectSql = "select param_name, param_value from  TBLPARAMETERS";

        try {
            //connection = dbh.getDBConnection();

            // check that the login_id to create does not exist
            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, userId);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                exist = true;
            }else{
            	System.out.println("user not found: selectSql: " + selectSql);
            	System.out.println("user not found: merchant: " + merchant);
            	System.out.println("user not found: userId: " + userId);
            	//exist = true;
            }
            
			pstmtQuery.close();
            rs.close();
			pstmtQuery=null;
			rs=null;

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.userExist() SQL execution : " + selectSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			//if (connection !=null)
			   // dbh.freeDBConnection (connection);
		}

	    return exist;

	}

	
	
	/**Check for user existence before invoking login.
	 * 
	 * @param merchant
	 * @param userId
	 * @return
	 */
	public boolean userExist (String merchant, String userId) {
	    boolean exist = false;
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

        // check user logon id
        String selectSql = "SELECT LOGIN_ID FROM " + Constants.DB_TBL_USER + " WHERE MER_NAME = ? AND LOGIN_ID= ?  ";

        try {
            connection = dbh.getDBConnection();

            // check that the login_id to create does not exist
            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, userId);
			
			System.out.println("CPA.SecUsersHome.java userExist selectSql="+selectSql);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                exist = true;
            }
			pstmtQuery.close();
            rs.close();
			pstmtQuery=null;
			rs=null;

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.userExist() SQL execution : " + selectSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

	    return exist;

	}

	
	
	/**Get no. of bad password attempts
	 * 
	*/
	public int getNoOfBadPwdAttempts (String merchant, String userId, Connection connection) {
	    int num = -1;
        //Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        //DBAccessHome dbh = DBAccessHome.getInstance();

        // check user logon id
        String selectSql = "SELECT BAD_PWD_NO_ATTEMPTS FROM " + Constants.DB_TBL_USER + " WHERE MER_NAME = ? AND LOGIN_ID= ?  ";

        try {
            //connection = dbh.getDBConnection();

            // check that the login_id to create does not exist
            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, userId);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                num = rs.getInt(1);
                System.out.println(num);
            }
			pstmtQuery.close();
            rs.close();
			pstmtQuery=null;
			rs=null;

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.userExist() SQL execution : " + selectSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			//if (connection !=null)
			    //dbh.freeDBConnection (connection);
		}
        num = 1; //force bad password attempt to 1 (debug mode by: L)
	    return num;

	}
	
	
	
	 /** Get no. of bad password attempts
	 * @param merchant
	 * @param userId
	 * @return
	 */
	public int getNoOfBadPwdAttempts (String merchant, String userId) {
	    int num = -1;
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

        // check user logon id
        String selectSql = "SELECT BAD_PWD_NO_ATTEMPTS FROM " + Constants.DB_TBL_USER + " WHERE MER_NAME = ? AND LOGIN_ID= ?  ";

        try {
            connection = dbh.getDBConnection();

            // check that the login_id to create does not exist
            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, userId);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                num = rs.getInt(1);
            }
			pstmtQuery.close();
            rs.close();
			pstmtQuery=null;
			rs=null;

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.userExist() SQL execution : " + selectSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

	    return num;

	}

	
	
	 /**Increase the no of bad password attempts by one for the given user id
	 * @param merchant
	 * @param userId user login id
	 * @return true if update successfull
	 */
	public boolean increaseNoOfBadPwdAttempts (String merchant, String userId, Connection connection) {

	    boolean success = true;
        //Connection connection = null;
        PreparedStatement pstmtUpdate = null;
        int noOfRecords=0;

        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        //DBAccessHome dbh = DBAccessHome.getInstance();

        // update user table, increase bad_pwd_no_attempts by 1
        String upadateSql = " update " + Constants.DB_TBL_USER + " set bad_pwd_no_attempts = bad_pwd_no_attempts + 1 where login_id = ? and mer_name = ? ";

        try {
            //connection = dbh.getDBConnection();
			sysLogMgr.logMsg("SecUsersHome.increaseNoOfBadPwdAttempts() Step 05C userId : [" + userId + "]-Increase BadPwdAttempts -upadateSql =[" + upadateSql + "]");

            pstmtUpdate =  connection.prepareStatement(upadateSql);
            pstmtUpdate.setString(1, userId);
            pstmtUpdate.setString(2, merchant);

            // execute query to update record
            noOfRecords = pstmtUpdate.executeUpdate();
			pstmtUpdate.close();
			pstmtUpdate=null;
			sysLogMgr.logMsg("SecUsersHome.increaseNoOfBadPwdAttempts() Step 05C userId : [" + userId + "]-Increase BadPwdAttempts -noOfRecords =[" + noOfRecords + "]");

            if (noOfRecords <=0)
                success = false;

        } catch (java.sql.SQLException sqle) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.increaseNoOfBadPwdAttempts() SQL execution : " + upadateSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtUpdate != null) {
				try {
				    pstmtUpdate.close();
				} catch (SQLException e1) {
				}
			}
			//if (connection !=null)
			//    dbh.freeDBConnection (connection);
		}

        return success;
	}

	
	
	/**Update last login status of user.
	 * 
	 * 
	 * 
	*/
	public boolean updateLastLogin (String merchant,  String userId, String loginIpAddress) {

	    boolean success = true;
        Connection connection = null;
        PreparedStatement pstmtUpdate = null;
        int noOfRecords=0;
		java.util.Date now = new java.util.Date();

        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

		// update user table upon login
		String updateSql = "UPDATE " + Constants.DB_TBL_USER + " SET LAST_LOGIN_TIMESTAMP = ?, " +
				"LAST_LOGIN_IP = ?,  BAD_PWD_NO_ATTEMPTS = 0, LOCKED = '" + Constants.DB_LOGIN_LOCKED +
				"' WHERE LOGIN_ID = ? AND MER_NAME = ? ";

        try {
            connection = dbh.getDBConnection();
      		connection.setAutoCommit(false);

            // finally update last login timestamp and IP, update bad_pwd_attempts to 0 and locked = logged in
            // Now update the user record
            pstmtUpdate = connection.prepareStatement(updateSql);
            pstmtUpdate.setTimestamp(1, new java.sql.Timestamp(now.getTime()));
            pstmtUpdate.setString(2, loginIpAddress);
            pstmtUpdate.setString(3, userId);
            pstmtUpdate.setString(4, merchant);

	        // execute the insert statement;
	        noOfRecords = pstmtUpdate.executeUpdate();
			pstmtUpdate.close();
			pstmtUpdate=null;
            sysLogMgr.logMsg("Error in SecUsersHome.updateLastLogin() SQL execution userId: " + updateSql);
			connection.commit();
			connection.setAutoCommit(true);

            if (noOfRecords <=0)
                success = false;

        } catch (java.sql.SQLException sqle) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.updateLastLogin() SQL execution userId: " + updateSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtUpdate != null) {
				try {
				    pstmtUpdate.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

        return success;
	}
	
	
	
	/**Logout user status flag.
	 * 
	 * 
	 * 
	*/
	public boolean logout (String merchant,  String userId) {

	    boolean success = true;
        Connection connection = null;
        PreparedStatement pstmtUpdate = null;
        int noOfRecords=0;

        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

        // update user table, set the locked flag
        String upadateSql = " update " + Constants.DB_TBL_USER + " set locked= ? where mer_name=? and login_id = ? ";

        try {
            connection = dbh.getDBConnection();
      		connection.setAutoCommit(false);

            // update the locked flag
            pstmtUpdate =  connection.prepareStatement(upadateSql);
            pstmtUpdate.setString(1, Constants.DB_NO);
            pstmtUpdate.setString(2, merchant);
            pstmtUpdate.setString(3, userId);

            // execute query to update
            noOfRecords = pstmtUpdate.executeUpdate();
			pstmtUpdate.close();
			pstmtUpdate=null;
			connection.commit();
			connection.setAutoCommit(true);

            if (noOfRecords <=0)
                success = false;

        } catch (java.sql.SQLException sqle) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.logout() SQL execution : " + upadateSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtUpdate != null) {
				try {
				    pstmtUpdate.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

        return success;
	}

	
	
	/**Unlock user status.
	 * 
	 * 
	*/
	public boolean unLockUser (String merchant, String userId) {

	    boolean success = true;
        Connection connection = null;
        PreparedStatement pstmtUpdate = null;
        int noOfRecords=0;

        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

        // update user table, set bad_pwd_no_attempts to 0, locked = n
        String upadateSql = " update " + Constants.DB_TBL_USER + " set bad_pwd_no_attempts = 0, locked= ? where mer_name = ? and login_id = ? ";

        try {
            connection = dbh.getDBConnection();

            // update the locked flag
            pstmtUpdate =  connection.prepareStatement(upadateSql);
            pstmtUpdate.setString(1, Constants.DB_NO);
            pstmtUpdate.setString(2, merchant);
            pstmtUpdate.setString(3, userId);

            // execute query to update
            noOfRecords = pstmtUpdate.executeUpdate();
            //System.out.println("SecUsersHome.unLockUser() : noOfRecords=" + noOfRecords);
			pstmtUpdate.close();
			pstmtUpdate=null;
            if (noOfRecords <=0)
                success = false;

        } catch (java.sql.SQLException sqle) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.unLockUser() SQL execution : " + upadateSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtUpdate != null) {
				try {
				    pstmtUpdate.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

        return success;

	}

	
	
	/**Reset user password.
	 * 
	 * 
	*/
	public boolean resetUserPwd (String merchant, String userId, String newPwd) throws LogonException {

	    boolean success = true;
        Connection connection = null;
        PreparedStatement pstmtUpdate = null;
        int noOfRecords=0;
        java.util.Date now = new java.util.Date();

        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
        String resetPwdFlag = ParamHome.getInstance().getParamValue(Constants.APP_MERCHANT, ParamHome.PARAM_TYPE_TECH, ParamHome.TECH_PARAM_PASSWORD_RESET_FLAG);
        String upadateSql = " update " + Constants.DB_TBL_USER + " set password = ?, bad_pwd_no_attempts = 0, locked= ?, reset_pwd_flag= ?, last_pwd_changed= ? where mer_name = ? and login_id = ? ";

        String allowPwdSameUserId = ParamHome.getInstance().getParamValue(merchant, ParamHome.PARAM_TYPE_SEC, ParamHome.SEC_ALLOW_SAME_USER_ID_PW);

        if (allowPwdSameUserId.equals(Constants.DB_NO)) {
            if (newPwd.equals(userId)) {
                throw new LogonException(LogonException.USER_ID_PWD_SAME);
            }
        }

        try {
            connection = dbh.getDBConnection();

            // update the locked flag
            pstmtUpdate =  connection.prepareStatement(upadateSql);
            pstmtUpdate.setString(1, MD5Helper.hash(newPwd));
            pstmtUpdate.setString(2, Constants.DB_NO);
            pstmtUpdate.setString(3, resetPwdFlag);
            //pstmtUpdate.setString(3, Constants.DB_YES);
            pstmtUpdate.setTimestamp(4, new Timestamp(now.getTime()));
            pstmtUpdate.setString(5, merchant);
            pstmtUpdate.setString(6, userId);

            // execute query to update
            noOfRecords = pstmtUpdate.executeUpdate();
			pstmtUpdate.close();
			pstmtUpdate=null;

            if (noOfRecords <=0)
                success = false;

        } catch (NoSuchAlgorithmException nsae) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.resetUserPwd() MD5 Hash error : " + "\n" + nsae);
            nsae.printStackTrace();
        } catch (UnsupportedEncodingException uee) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.resetUserPwd() MD5 Hash error : " + "\n" + uee);
            uee.printStackTrace();
        } catch (java.sql.SQLException sqle) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.resetUserPwd() SQL execution : " + upadateSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtUpdate != null) {
				try {
				    pstmtUpdate.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

        return success;

	}

	
	
	/**Check if the logon user can approve the given user
	 * 
	 * (user created must be approved before active)
	 * Condition :
	 * - The user CREATE_STATUS is not approved yet (Constants.DB_USER_CREATE_APPROVE)
	 * - Logon user ID not same with CREATED_BY user id
	 * - Logon user role must be same with CREATED_BY user role
	 * - Logon user agency must be same with CREATED_BY user agency
	 * @param merchant
	 * @param loginId
	 * @param logonUserId
	 * @return logon user is allow to approve given user or not
	 */
	public boolean isApproveAllow (String merchant, String loginId, String logonUserId) {
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
        boolean allow = true;
        String createdByUserId ="";

        String selectSql =  "SELECT CREATED_BY, CREATE_STATUS FROM "
                        + Constants.DB_TBL_USER +
                        " WHERE MER_NAME = ? AND LOGIN_ID= ? ";

        String selectSql2 =  "SELECT AGENCY_CODE, ROLE_CODE FROM "
            + Constants.DB_TBL_USER +
            " WHERE MER_NAME = ? AND LOGIN_ID= ? ";

        try {
            connection = dbh.getDBConnection();

            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, loginId);

            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                createdByUserId = rs.getString(1);
                if (rs.getString(2).equals(Constants.DB_USER_CREATE_APPROVE)) {
                    allow = false;
                }else if (createdByUserId.equals(logonUserId)) {
                    allow = false;
                }
            } else {
                allow = false;
            }
			pstmtQuery.close();
            rs.close();
			pstmtQuery=null;
            rs = null;

            // check role code and agency code
            String logonUserRoleCode="", createdByUserRoleCode="";
            String logonUserAgencyCode="", createdByUserAgencyCode="";

            if (allow) {
                pstmtQuery =  connection.prepareStatement(selectSql2);
                pstmtQuery.setString(1, merchant);
                pstmtQuery.setString(2, createdByUserId);

                rs = pstmtQuery.executeQuery();
                if (rs.next()) {
                    createdByUserAgencyCode = rs.getString(1);
                    createdByUserRoleCode = rs.getString(2);
                }
                rs.close();
                rs = null;

                pstmtQuery =  connection.prepareStatement(selectSql2);
                pstmtQuery.setString(1, merchant);
                pstmtQuery.setString(2, logonUserId);

                rs = pstmtQuery.executeQuery();
                if (rs.next()) {
                    logonUserAgencyCode = rs.getString(1);
                    logonUserRoleCode = rs.getString(2);
                }
                rs.close();
                rs = null;

                if (logonUserAgencyCode.equals("") || logonUserRoleCode.equals("") ||
                        createdByUserAgencyCode.equals("") || createdByUserRoleCode.equals("") ) {
                    allow = false;
                }else if (!logonUserAgencyCode.equals(createdByUserAgencyCode) ) {
                    allow = false;
                } else if (!logonUserRoleCode.equals(createdByUserRoleCode) ) {
                    allow = false;
                }
            }

        } catch (java.sql.SQLException sqle) {
            allow = false;
            sysLogMgr.logMsg("Error in SecUsersHome.isApproveAllow() SQL execution : sql=" + selectSql + "\n " + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

	    return allow;

	}

	
	
	/**Logon user approve the given user
	 * 
	 * (user created must be approved before active)
	 *
	 * @param merchant
	 * @param loginId
	 * @param logonUserid
	 * @return user approved successfully or not
	 */
	public boolean approveUser (String merchant, String loginId, String logonUserId) {
        Connection connection = null;
        PreparedStatement pstmtUpdate = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
        boolean success = true;
        int noOfRecords = 0;

        if (!isApproveAllow(merchant, loginId, logonUserId)) {
            return false;
        }

        String updateSql = "UPDATE "
            + Constants.DB_TBL_USER
            + " SET CREATE_STATUS = ?, LAST_UPDATED_BY = ?, LAST_UPDATED_TIMESTAMP = ?  " +
            		" WHERE MER_NAME= ? AND LOGIN_ID = ? ";

	    try {
	        java.util.Date now = new java.util.Date();
	        connection = dbh.getDBConnection();

	        pstmtUpdate = connection.prepareStatement(updateSql);
	        pstmtUpdate.setString(1, Constants.DB_USER_CREATE_APPROVE);
	        pstmtUpdate.setString(2, logonUserId);
	        pstmtUpdate.setTimestamp(3, new Timestamp(now.getTime()));
	        pstmtUpdate.setString(4, merchant);
	        pstmtUpdate.setString(5, loginId);

	        // execute the update statement;
	        noOfRecords = pstmtUpdate.executeUpdate();
			pstmtUpdate.close();
			pstmtUpdate=null;
	        if (success && noOfRecords < 1 )
	            success = false;

	    } catch (java.sql.SQLException sqle) {
	        success  = false;
	        sysLogMgr.logMsg("Error in SecUsersHome.approveUser() SQL execution : " + updateSql + "\n" + sqle);
	        sqle.printStackTrace();
	    } finally {
			if (pstmtUpdate != null) {
				try {
				    pstmtUpdate.close();
				} catch (SQLException e1) {
				}
			}

			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

	    return success;

	}

	
	
	/**Check for user existence before invoking login
	 * 
	 * @param merchant
	 * @param userId
	 * @return
	 */

	public boolean isUserLogon (String merchant, String userId) {
	    boolean logon = false;
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

        // check user logon id
        String selectSql = "SELECT LOCKED FROM " + Constants.DB_TBL_USER + " WHERE MER_NAME = ? AND LOGIN_ID= ?  ";

        try {
            connection = dbh.getDBConnection();
            String locked = null;

            // check that the login_id to create does not exist
            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, userId);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                locked = rs.getString(1);
                if (locked.equals(Constants.DB_LOGGED_IN)) {
                    logon = true;
                }
            }
			pstmtQuery.close();
            rs.close();
			pstmtQuery=null;
			rs=null;

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.isUserLogon() SQL execution : " + selectSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

	    return logon;

	}

	
	
	/**Get user
	 * 
	 * @param merchant
	 * @param loginId
	 * @return
	 */
	public UserDao getUser (String merchant, String loginId) {
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
        UserDao user = new UserDao();

        System.out.println("User edit begin..");
        System.out.println(merchant);
        System.out.println(loginId);
        String selectSql =  "SELECT A.ID, A.LOGIN_ID, A.NAME, A.ROLE_CODE, A.ENABLED, A.LOCKED, A.REMARKS, A.NO_ACCESS_DATE, B.ROLE_NAME, A.COUNTRY_CODE, A.EMAIL  FROM "
                + Constants.DB_TBL_USER + " A, " + Constants.DB_TBL_ACCESSROLE + " B WHERE A.MER_NAME = ? AND A.LOGIN_ID= ? AND A.ROLE_CODE = B.ROLE_CODE ";
        try {
            connection = dbh.getDBConnection();
            PropertyHome ph = PropertyHome.getInstance();

            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, loginId);
            System.out.println("The selectSql string is "+selectSql);
            
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                user.setUserDBId(rs.getLong(1));
                user.setUserLogin(rs.getString(2));
                user.setUserName(rs.getString(3));
//                user.setAgencyCode(rs.getString(4));
                user.setUserRole(rs.getString(4));
                user.setUserEnabled(rs.getString(5));

                if (rs.getString(6).equals(Constants.DB_YES)) {
                    user.setUserLocked(true);
                } else {
                    user.setUserLocked(false);
                }

                user.setRemark(rs.getString(7));

                java.util.Date noAccessDate = rs.getDate(8);
                if (noAccessDate == null ) {
                    user.setBlockAccessDate("");
                } else {
                    user.setBlockAccessDate(DateUtil.formatLocalSlash(noAccessDate));
                }

                user.setRoleName(rs.getString(9));
                
                user.setCountryCode(rs.getString("COUNTRY_CODE"));
                user.setEmail(rs.getString("EMAIL"));
                
                
                // get email and contact No
                //user.setEmail(ph.getProperty(Long.toString(user.getUserDBId()), user, UserDao.PROP_EMAIL));
                //user.setHandPhoneNo(ph.getProperty(Long.toString(user.getUserDBId()), user, UserDao.PROP_HANDPHONE_NO));

            }
			pstmtQuery.close();
            rs.close();
			pstmtQuery=null;
			rs=null;

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.getUsers() SQL execution : sql=" + selectSql + "\n " + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

	    return user;

	}
	
	
	
	/**Update existing user
	 * 
	 * @param user
	 * @param merchant
	 * @param updatedBy
	 * @param remarks
	 * @return
	 */
	public boolean updateUser (UserDao user, String updatedBy) throws LogonException {

	    boolean success = true;
        Connection connection = null;
        PreparedStatement pstmtUpdate = null;
        int noOfRecords=0;

        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
        java.util.Date now = new java.util.Date();
        ParamHome ph = ParamHome.getInstance();
        String resetPwdFlag = ph.getParamValue(Constants.APP_MERCHANT, ParamHome.PARAM_TYPE_TECH, ParamHome.TECH_PASSWORD_RESET_FLAG);
        String allowPwdSameUserId = ph.getParamValue(Constants.APP_MERCHANT, ParamHome.PARAM_TYPE_SEC, ParamHome.SEC_ALLOW_SAME_USER_ID_PW);

        //System.out.println("...SecUsersHome.updateUser() : resetPwdFlag=" +resetPwdFlag);

        if (allowPwdSameUserId.equals(Constants.DB_NO)) {
            if (user.getLoginPassword().equals(user.getUserLogin())) {
                throw new LogonException(LogonException.USER_ID_PWD_SAME);
            }
        }

        // update user record into user table
        String updateSql = "UPDATE "
                + Constants.DB_TBL_USER
                + " SET NAME = ? , AGENCY_CODE = ?, ROLE_CODE = ?, LAST_UPDATED_TIMESTAMP = ?,"
                + " LAST_UPDATED_BY = ? , ENABLED = ?,  LOCKED = ?,  REMARKS = ?, NO_ACCESS_DATE = ?, COUNTRY_CODE = ? WHERE LOGIN_ID = ? ";

        String updatePwdSql = "UPDATE "
            + Constants.DB_TBL_USER
            + " SET PASSWORD = ? , BAD_PWD_NO_ATTEMPTS = ?, RESET_PWD_FLAG = ? WHERE LOGIN_ID = ? ";

        try {
            connection = dbh.getDBConnection();
            connection.setAutoCommit(false);

            // Added by Meau to add country code
            String agencyCode="";
            String countryCode="";
            if(user.getAgencyCode()!=null && !user.getAgencyCode().equals("")){
				StringTokenizer tokenizer = new StringTokenizer(user.getAgencyCode(),"_");
				agencyCode = tokenizer.nextToken();
				countryCode = tokenizer.nextToken();
			}

            // Now update user record
            pstmtUpdate = connection.prepareStatement(updateSql);
            pstmtUpdate.setString(1, user.getUserName());
            pstmtUpdate.setString(2, agencyCode);
            pstmtUpdate.setString(3, user.getUserRole());
            pstmtUpdate.setTimestamp(4, new Timestamp(now.getTime()));
            pstmtUpdate.setString(5, updatedBy);
            pstmtUpdate.setString(6, user.getUserEnabled());
            pstmtUpdate.setString(7, Constants.DB_NO);
            pstmtUpdate.setString(8, user.getRemark());

	        if (!user.getBlockAccessDate().equals("")) {
	            java.util.Date blockDate = DateUtil.createDateLocalSlash(user.getBlockAccessDate());
	            pstmtUpdate.setDate(9, new java.sql.Date(blockDate.getTime()));
	        } else {
	            pstmtUpdate.setNull(9, Types.DATE);
	        }

            pstmtUpdate.setString(10, countryCode);
            pstmtUpdate.setString(11, user.getUserLogin());

	        // execute the update statement;
	        noOfRecords = pstmtUpdate.executeUpdate();
			pstmtUpdate.close();
			pstmtUpdate=null;
	        if (success && noOfRecords < 1 )
	            success = false;

	        if (success && !user.getLoginPassword().equals("")) {
	            // update the password if it has been changed
	            pstmtUpdate = connection.prepareStatement(updatePwdSql);
	            pstmtUpdate.setString(1, MD5Helper.hash(user.getLoginPassword()));
	            pstmtUpdate.setInt(2, 0);
	            //pstmtUpdate.setString(3, Constants.DB_YES);
	            pstmtUpdate.setString(3, resetPwdFlag);
	            pstmtUpdate.setString(4, user.getUserLogin());

		        // execute the update statement;
		        noOfRecords = pstmtUpdate.executeUpdate();
				pstmtUpdate.close();
				pstmtUpdate=null;
		        if (noOfRecords < 1 )
		            success = false;

	        }

        } catch (ParseException pe) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.updateUser() error parsing date : " + "\n" + pe);
            pe.printStackTrace();
        } catch (NoSuchAlgorithmException nsae) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.updateUser() MD5 Hash error : " + "\n" + nsae);
            nsae.printStackTrace();
        } catch (UnsupportedEncodingException uee) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.updateUser() MD5 Hash error : " + "\n" + uee);
            uee.printStackTrace();
        } catch (java.sql.SQLException sqle) {
            success  = false;
            sysLogMgr.logMsg("Error in SecUsersHome.updateUser() SQL execution : " + updateSql + "\n" + updatePwdSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtUpdate != null) {
				try {
				    pstmtUpdate.close();
				} catch (SQLException e1) {
				}
			}
			if (!success) {
			    try {
			        connection.rollback();
			    } catch (SQLException sqle) {
		            sysLogMgr.logMsg("Error in connection rollback for SecUsersHome.updateUser()\n" + sqle);
		            sqle.printStackTrace();
			    }
			} else {
			    try {
					connection.commit();
					connection.setAutoCommit(true);
			    } catch (SQLException sqle) {
		            sysLogMgr.logMsg("Error in connection rollback for SecUsersHome.updateUser()\n" + sqle);
		            sqle.printStackTrace();
			    }

			}

			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

        return success;

	}

	
	
	/**This is to get Security Group Users
	 * 	
	 * 	Author 	: 	Michael Kang
	 *	Date	:	13/02/2009
	 *
	 */
	public ArrayList findSecGroupUserList(String strMerchant, String strSelectedGroup) {
				////System.out.println("********************"+newIcNo);

		ArrayList secGroupLevelList = new ArrayList();
		ArrayList secGroupUserList = new ArrayList();

		int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
		DBConnectionManager connMgr;
		String connectionPool;
		SystemLogManager sysLogMgr;

		connectionPool = ConfigManager.getInstance().getConnectionPool();
		connMgr = DBConnectionManager.getInstance();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		sysLogMgr = SystemLogManager.getInstance();

		Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

		PreparedStatement stmt = null;
		ResultSet rs= null;
		PreparedStatement stmt2 = null;
		ResultSet rs2= null;
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");

		try {
			if (conn == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation("SecUsersHome_findSecGroupUserList_001 [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			} //if (conn == null)
			else{
				StringBuffer sqlStmt2 = new StringBuffer("");
				sqlStmt2.append("SELECT * FROM " + Constants.DB_TBL_USER + "  WHERE MER_NAME = '" + strMerchant + "' AND ROLE_CODE='"+strSelectedGroup+"' ORDER BY NAME ASC");
				//System.out.println("SecUsersHome_findSecGroupUserList_003 : " + sqlStmt2);

				stmt2 = conn.prepareStatement(sqlStmt2.toString());

				rs2 = stmt2.executeQuery();
				////System.out.println("Resultset . next()");
				String sLoginID = "";
				String sUserName = "";
				String sFullName = "";
				String sEmail = "";
				String sDept = "";
				String sCountryCode = "";
				String sLocked = "";
				Date dLastLogin = null;
				int dormantdays = 0;
				while (rs2.next()){
					SecUserDao secuserdao = new SecUserDao();

					sLoginID = "";
					sLoginID = rs2.getString("LOGIN_ID");
					if (sLoginID != null){
						sLoginID = sLoginID.trim();
					}

					sUserName = "";
					sUserName = rs2.getString("NAME");
					if (sUserName != null){
						sUserName = sUserName.trim();
					}
					else
					{
						sUserName = "";
					}
					
					sFullName = "";
					sFullName = rs2.getString("FULL_NAME");
					if (sFullName != null){
						sFullName = sFullName.trim();
					}
					else
					{
						sFullName = "";
					}
					
					sEmail = "";
					sEmail = rs2.getString("EMAIL");
					if (sEmail != null){
						sEmail = sEmail.trim();
					}
					else
					{
						sEmail = "";
					}
					
					sDept = "";
					sDept = rs2.getString("DEPT");
					if (sDept != null){
						sDept = sDept.trim();
					}
					else
					{
						sDept = "";
					}
					
					sCountryCode = "";
					sCountryCode = rs2.getString("COUNTRY_CODE");
					if (sCountryCode != null){
						sCountryCode = sCountryCode.trim();
					}
					else
					{
						sCountryCode = "";
					}
					
					sLocked = "";
					sLocked = rs2.getString("LOCKED");
					if (sLocked != null){
						sLocked = sLocked.trim();
					}
					else
					{
						sLocked = "";
					}
					
					dLastLogin = rs2.getTimestamp("LAST_LOGIN_TIMESTAMP");
					if (dLastLogin != null){
						Date sysdate = new Date();
						long startDateTime = dLastLogin.getTime();
					    long endDateTime = sysdate.getTime();
					    long milPerDay = 1000*60*60*24; 
					    
					    dormantdays = (int) ((endDateTime - startDateTime) / milPerDay); 
					} else {
						dormantdays = 0;
					}
					
					//System.out.println("SecUsersHome_findSecGroupUserList_003 : sLoginID " + sLoginID);
					secuserdao.set(sLoginID,strSelectedGroup,"", "");
					secuserdao.setUserName(sUserName);
					secuserdao.setFullName(sFullName);
					secuserdao.setEmail(sEmail);
					secuserdao.setCountryCode(sCountryCode);
					secuserdao.setLocked(sLocked);
					secuserdao.setDept(sDept);
					secuserdao.setDormantDays(dormantdays);
					secGroupUserList.add(secuserdao);
				}
				stmt2.close();
				rs2.close();
				stmt2=null;
				rs2=null;

			} //[ELSE] if (conn == null)
		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("SecUsersHome_findSecGroupUserList_002 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("SecUsersHome_findSecGroupUserList_003 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (stmt2 != null) {
				try {
					stmt2.close();
				} catch (SQLException e1) {}
			}
			connMgr.freeConnection(connectionPool, conn);
		}

				////System.out.println("Resultset12 . next()");
		return secGroupUserList;
	}
	
	/**This is to get user by userID
	 * 	
	 * 	Author 	: 	Ricky Khoo
	 *	Date	:	22/06/2014
	 *
	 */
	public ArrayList findSecGroupUserList(String strUserID, String strMerchant, String strSelectedGroup) {
				////System.out.println("********************"+newIcNo);

		ArrayList secGroupLevelList = new ArrayList();
		ArrayList secGroupUserList = new ArrayList();

		int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
		DBConnectionManager connMgr;
		String connectionPool;
		SystemLogManager sysLogMgr;

		connectionPool = ConfigManager.getInstance().getConnectionPool();
		connMgr = DBConnectionManager.getInstance();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		sysLogMgr = SystemLogManager.getInstance();

		Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

		PreparedStatement stmt = null;
		ResultSet rs= null;
		PreparedStatement stmt2 = null;
		ResultSet rs2= null;
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");

		try {
			if (conn == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation("SecUsersHome_findSecGroupUserList_001 [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			} //if (conn == null)
			else{
				StringBuffer sqlStmt2 = new StringBuffer("");
				sqlStmt2.append("SELECT b.*, a.role_name FROM " + Constants.DB_TBL_USER + " b," + 
								Constants.DB_TBL_ACCESSROLE + " a  WHERE b.role_code = a.role_code AND b.MER_NAME = '" + strMerchant + "' AND b.ROLE_CODE='"+strSelectedGroup+"' AND b.LOGIN_ID='"+strUserID+"'");
				//System.out.println("SecUsersHome_findSecGroupUserList_003 : " + sqlStmt2);

				stmt2 = conn.prepareStatement(sqlStmt2.toString());

				rs2 = stmt2.executeQuery();
				////System.out.println("Resultset . next()");
				String sLoginID = "";
				String sUserName = "";
				String sFullName = "";
				String sEmail = "";
				String sDept = "";
				String sCountryCode = "";
				String sLocked = "";
				Date dLastLogin = null;
				int dormantdays = 0;
				int badPwdAttempts = 0;
				String enable = "";
				String currentLogon = "";
				String createBy = "";
				Date 	dCreateDate = null;
				String createDate = "";
				String updateBy = "";
				Date	dLastPwdChange = null;
				String lastPwdChange = "";
				Date 	dLastUpdateDate = null;
				String lastUpdateDate = "";
				String lastLoginTimestamp = "";
				String region = "";
				String roleName = "";
				while (rs2.next()){
					SecUserDao secuserdao = new SecUserDao();

					sLoginID = "";
					sLoginID = rs2.getString("LOGIN_ID");
					if (sLoginID != null){
						sLoginID = sLoginID.trim();
					}

					sUserName = "";
					sUserName = rs2.getString("NAME");
					if (sUserName != null){
						sUserName = sUserName.trim();
					}
					else
					{
						sUserName = "";
					}
					
					sFullName = "";
					sFullName = rs2.getString("FULL_NAME");
					if (sFullName != null){
						sFullName = sFullName.trim();
					}
					else
					{
						sFullName = "";
					}
					
					sEmail = "";
					sEmail = rs2.getString("EMAIL");
					if (sEmail != null){
						sEmail = sEmail.trim();
					}
					else
					{
						sEmail = "";
					}
					
					sDept = "";
					sDept = rs2.getString("DEPT");
					if (sDept != null){
						sDept = sDept.trim();
					}
					else
					{
						sDept = "";
					}
					
					sCountryCode = "";
					sCountryCode = rs2.getString("COUNTRY_CODE");
					if (sCountryCode != null){
						sCountryCode = sCountryCode.trim();
					}
					else
					{
						sCountryCode = "";
					}
					
					sLocked = "";
					sLocked = rs2.getString("LOCKED");
					if (sLocked != null){
						sLocked = sLocked.trim();
					}
					else
					{
						sLocked = "";
					}
					
					dLastLogin = rs2.getTimestamp("LAST_LOGIN_TIMESTAMP");
					if (dLastLogin != null){
						Date sysdate = new Date();
						long startDateTime = dLastLogin.getTime();
					    long endDateTime = sysdate.getTime();
					    long milPerDay = 1000*60*60*24; 
					    
					    dormantdays = (int) ((endDateTime - startDateTime) / milPerDay); 
					} else {
						dormantdays = 0;
					}
					
					badPwdAttempts = rs2.getInt("BAD_PWD_NO_ATTEMPTS"); 
					
					enable = "";
					enable = rs2.getString("ENABLED");
					if (enable != null){
						enable = enable.trim();
					}
					else
					{
						enable = "";
					} 
					
					if(dLastLogin != null){
						currentLogon = "Y";
					} else {
						currentLogon = "N";
					}
					
					createBy = "";
					createBy = rs2.getString("CREATED_BY");
					if (createBy != null){
						createBy = createBy.trim();
					}
					else
					{
						createBy = "";
					}
					 
					dCreateDate = rs2.getTimestamp("CREATED_TIMESTAMP");
					if (dCreateDate != null){ 
						createDate = dCreateDate.toString();
						createDate = createDate.substring(0, createDate.indexOf("."));
					}
					else
					{
						createDate = "";
					}
					
					updateBy = "";
					updateBy = rs2.getString("LAST_UPDATED_BY");
					if (updateBy != null){
						updateBy = updateBy.trim();
					}
					else
					{
						updateBy = "";
					}
					 
					dLastPwdChange = rs2.getTimestamp("LAST_PWD_CHANGED");
					if (dLastPwdChange != null){
						lastPwdChange = dLastPwdChange.toString();
						lastPwdChange = lastPwdChange.substring(0,lastPwdChange.indexOf("."));
					}
					else
					{
						lastPwdChange = "";
					}
					 
					dLastUpdateDate = rs2.getTimestamp("LAST_UPDATED_TIMESTAMP");
					if (dLastUpdateDate != null){
						lastUpdateDate = dLastUpdateDate.toString();
						lastUpdateDate = lastUpdateDate.substring(0, lastUpdateDate.indexOf("."));
					}
					else
					{
						lastUpdateDate = "";
					}
					
					region = "";
					region = rs2.getString("REGION");
					if (region != null){
						region = region.trim();
					}
					else
					{
						region = "";
					}
					
					roleName = "";
					roleName = rs2.getString("role_name");
					if (roleName != null){
						roleName = roleName.trim();
					}
					else
					{
						roleName = "";
					}
					
					//System.out.println("SecUsersHome_findSecGroupUserList_003 : sLoginID " + sLoginID);
					secuserdao.set(sLoginID,strSelectedGroup + " - "+ roleName,"", "");
					secuserdao.setUserName(sUserName);
					secuserdao.setFullName(sFullName);
					secuserdao.setEmail(sEmail);
					secuserdao.setCountryCode(sCountryCode);
					secuserdao.setLocked(sLocked);
					secuserdao.setDept(sDept);
					secuserdao.setDormantDays(dormantdays);
					secuserdao.setBadPwdAttempts(badPwdAttempts);
					secuserdao.setEnable(enable);
					secuserdao.setCurrentLogon(currentLogon);
					secuserdao.setCreateBy(createBy);
					secuserdao.setCreateDate(createDate);
					secuserdao.setUpdateBy(updateBy);
					secuserdao.setLastLoginDateTime(lastLoginTimestamp);
					secuserdao.setLastPwdChange(lastPwdChange);
					secuserdao.setLastUpdateDate(lastUpdateDate);
					secuserdao.setRegion(region);
					secGroupUserList.add(secuserdao);
				}
				stmt2.close();
				rs2.close();
				stmt2=null;
				rs2=null;

			} //[ELSE] if (conn == null)
		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("SecUsersHome_findSecGroupUserList_002 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("SecUsersHome_findSecGroupUserList_003 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (stmt2 != null) {
				try {
					stmt2.close();
				} catch (SQLException e1) {}
			}
			connMgr.freeConnection(connectionPool, conn);
		}

				////System.out.println("Resultset12 . next()");
		return secGroupUserList;
	}

	
	
	/** Create the given user
	 *
	 * @param user
	 * @param merchant
	 * @param updatedBy
	 * @param remarks
	 * @return
	 */
	public long createUser (UserDao user, String merchant, String updatedBy) throws UniqueKeyViolationException, LogonException {

	    boolean success = true;
      Connection connection = null;
      PreparedStatement pstmtInsert = null, pstmtQuery = null;
      int noOfRecords=0;
      long userDBId=-1;

      SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
      DBAccessHome dbh = DBAccessHome.getInstance();
      java.util.Date now = new java.util.Date();
      String allowPwdSameUserId = ParamHome.getInstance().getParamValue(merchant, ParamHome.PARAM_TYPE_SEC, ParamHome.SEC_ALLOW_SAME_USER_ID_PW);

      if (allowPwdSameUserId.equals(Constants.DB_NO)) {
          if (user.getUserLogin().equals(user.getLoginPassword())) {
              throw new LogonException(LogonException.USER_ID_PWD_SAME);
          }
      }

      // get updated by user role
      UserDao updatedByUserDao = getUser (merchant, updatedBy);

      System.out.println("Start the user id insertion into database");
      // insert user record into user table
      String insertSql = "INSERT INTO " + Constants.DB_TBL_USER + " (LOGIN_ID, PASSWORD, NAME, MER_NAME," +
      		"ROLE_CODE, BAD_PWD_NO_ATTEMPTS, CREATED_BY, CREATED_TIMESTAMP, LAST_UPDATED_TIMESTAMP, " +
      		"LAST_UPDATED_BY, CREATE_STATUS, ENABLED, LOCKED, RESET_PWD_FLAG, REMARKS, NO_ACCESS_DATE, FULL_NAME, EMAIL, DEPT, REGION, COUNTRY_CODE)" +
      " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

      System.out.println("The insertSql statement is "+insertSql);
      String selectSql = "SELECT ID FROM " + Constants.DB_TBL_USER + " WHERE LOGIN_ID=? ";

      // check
      try {
          connection = dbh.getDBConnection();
          connection.setAutoCommit(false);

          // check that the login_id to create does not exist
          pstmtQuery =  connection.prepareStatement(selectSql);
          pstmtQuery.setString(1, user.getUserLogin());

          // execute query to get the user DB Id
          ResultSet rs = pstmtQuery.executeQuery();
          if (rs.next()) {
              success = false;
          }
          rs.close();

          if (success == false) {
              throw new UniqueKeyViolationException();
          }

          // Added by Meau to add country code
          String agencyCode="";
          String countryCode="";
          if(user.getAgencyCode()!=null && !user.getAgencyCode().equals("")){
				StringTokenizer tokenizer = new StringTokenizer(user.getAgencyCode(),"_");
				agencyCode = tokenizer.nextToken();
				countryCode = tokenizer.nextToken();
			}

          // Now insert the user record
          pstmtInsert = connection.prepareStatement(insertSql);
	        pstmtInsert.setString(1, user.getUserLogin());
	        pstmtInsert.setString(2, MD5Helper.hash(user.getLoginPassword()));
	        pstmtInsert.setString(3, user.getUserName());
	        pstmtInsert.setString(4, merchant);
	        pstmtInsert.setString(5, user.getUserRole());
	        pstmtInsert.setInt(6, 0);
	        pstmtInsert.setString(7, updatedBy);
	        pstmtInsert.setTimestamp(8, new Timestamp(now.getTime()));
	        pstmtInsert.setTimestamp(9, new Timestamp(now.getTime()));
	        pstmtInsert.setString(10, updatedBy);
//	        if (updatedByUserDao.getUserRole().equals(Constants.DB_USER_ROLE_SUPER)) {
	            pstmtInsert.setString(11, Constants.DB_USER_CREATE_APPROVE);
//	        } else {
//	            pstmtInsert.setString(11, Constants.DB_USER_CREATE_NEW);
//	        }
	        pstmtInsert.setString(12, user.getUserEnabled());
	        pstmtInsert.setString(13, Constants.DB_NO);
	        pstmtInsert.setString(14, "0");
	        pstmtInsert.setString(15, user.getRemark());


	        if (!user.getBlockAccessDate().equals("")) {
	            java.util.Date blockDate = DateUtil.createDateLocalSlash(user.getBlockAccessDate());
	            pstmtInsert.setDate(16, new java.sql.Date(blockDate.getTime()));
	        } else {	
	            pstmtInsert.setNull(16, Types.DATE);
	        }
	        pstmtInsert.setString(17, user.getFullName());
	        pstmtInsert.setString(18, user.getEmail());
	        pstmtInsert.setString(19, user.getUserDept());
	        pstmtInsert.setString(20, user.getUserRegion());
			pstmtInsert.setString(21, user.getCountryCode());

	        // execute the insert statement;
	        noOfRecords = pstmtInsert.executeUpdate();
			pstmtInsert.close();
			pstmtInsert=null;
	        if (success && noOfRecords < 1 )
	            success = false;

	        if (success) {
		        pstmtQuery =  connection.prepareStatement(selectSql);
	            pstmtQuery.setString(1, user.getUserLogin());

	            // execute query to get the user DB Id
	            rs = pstmtQuery.executeQuery();
	            if (rs.next()) {
	                userDBId = rs.getLong(1);
	            }
	            rs.close();
	        }
      } catch (ParseException pe) {
          success  = false;
          sysLogMgr.logMsg("Error in SecUsersHome.createUser() error parsing date : " + "\n" + pe);
          pe.printStackTrace();
      } catch (NoSuchAlgorithmException nsae) {
          success  = false;
          sysLogMgr.logMsg("Error in SecUsersHome.createUser() MD5 Hash error : " + "\n" + nsae);
          nsae.printStackTrace();
      } catch (UnsupportedEncodingException uee) {
          success  = false;
          sysLogMgr.logMsg("Error in SecUsersHome.createUser() MD5 Hash error : " + "\n" + uee);
          uee.printStackTrace();
      } catch (java.sql.SQLException sqle) {
          success  = false;
          sysLogMgr.logMsg("Error in SecUsersHome.createUser() SQL execution : " + insertSql + "\n" + sqle);
          sqle.printStackTrace();
      } finally {
			if (pstmtInsert != null) {
				try {
				    pstmtInsert.close();
				} catch (SQLException e1) {
				}
			}
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (!success) {
			    try {
			        connection.rollback();
			    } catch (SQLException sqle) {
		            sysLogMgr.logMsg("Error in connection rollback for SecUsersHome.createUser()\n" + sqle);
		            sqle.printStackTrace();
			    }
			} else {
			    try {
					connection.commit();
					connection.setAutoCommit(true);
			    } catch (SQLException sqle) {
		            sysLogMgr.logMsg("Error in connection commit for SecUsersHome.createUser()\n" + sqle);
		            sqle.printStackTrace();
			    }

			}

			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

      return userDBId;

	}

	
	
	/** Delete existing user and related properties
	 *
	 * @param loginId
	 * @param merchant
	 * @param updatedBy
	 * @param remarks
	 * @return
	 */
	public boolean removeUser (String loginId, String merchant, String updatedBy)  {

	    boolean success = true;
       Connection connection = null;
       PreparedStatement pstmtDelete = null;
       int noOfRecords=0;

       SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
       DBAccessHome dbh = DBAccessHome.getInstance();
       PropertyHome ph = PropertyHome.getInstance();

       long userDBId = getUserDBId(merchant, loginId);

       // update user record into user table
       String deleteSql = "DELETE FROM "
               + Constants.DB_TBL_USER
               + " WHERE LOGIN_ID = ? AND MER_NAME = ? ";
       

       try {

           if (userDBId < 0 ) {
               throw new EbppException("Invalid User DB Id for : " + loginId);
           }

           // Remove all the properties that belongs to the user
           //if (!ph.removeProperties(Long.toString(userDBId), new UserDao()) ||
               //!ph.removeProperties(Long.toString(userDBId), new SecUserDao()) ) {
               //throw new EbppException ("Fail to remove properties for user : " + loginId);
           //}

           connection = dbh.getDBConnection();

           // Now update user record
           pstmtDelete = connection.prepareStatement(deleteSql);
           pstmtDelete.setString(1, loginId);
           pstmtDelete.setString(2, merchant);
           System.out.println(" The sql statement is "+deleteSql);
           
	        // execute the update statement;
	        noOfRecords = pstmtDelete.executeUpdate();
			pstmtDelete.close();
			pstmtDelete=null;
	        if (success && noOfRecords < 1 )
	            success = false;

       } catch (java.sql.SQLException sqle) {
           success  = false;
           sysLogMgr.logMsg("Error in SecUsersHome.removeUser() SQL execution : " + deleteSql + "\n" + sqle);
           sqle.printStackTrace();
       } catch (EbppException ebppe) {
           success  = false;
           sysLogMgr.logMsg("Error in SecUsersHome.removeUser() :\n" + ebppe);
           ebppe.printStackTrace();
       } finally {
			if (pstmtDelete != null) {
				try {
				    pstmtDelete.close();
				} catch (SQLException e1) {
				}
			}

			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

       return success;

	}
	
	/** update edited user
	 *
	 * @param loginId
	 * @param merchant
	 * @param updatedBy
	 * @param remarks
	 * @return
	 */
	public boolean updateUser (UserDao user, String loginId, String merchant, String updatedBy)  {

		boolean success = true;
		Connection connection = null;
		PreparedStatement pstmtUpdate = null;
		int noOfRecords=0;

		SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
		DBAccessHome dbh = DBAccessHome.getInstance();

		long userDBId = getUserDBId(merchant, loginId);

		// update user record into user table
		String updateSql = "UPDATE "+Constants.DB_TBL_USER+" SET "
				+ "NAME = '"+user.getUserName()+"',"
				+ "FULL_NAME = '"+user.getFullName()+"',"
				+ "EMAIL = '"+user.getEmail()+"',"
				+ "DEPT = '"+user.getUserDept()+"',"
				+ "REGION = '"+user.getUserRegion()+"',"
				+ "COUNTRY_CODE = '"+user.getCountryCode()+"',"
				+ "LAST_UPDATED_BY ='"+updatedBy+"',"
				+ "LAST_UPDATED_TIMESTAMP = sysdate "
				+ "WHERE LOGIN_ID = '"+ loginId +"'";
 
		try {

			if (userDBId < 0 ) {
				throw new EbppException("Invalid User DB Id for : " + loginId);
			}

			connection = dbh.getDBConnection();

			// Now update user record
			pstmtUpdate = connection.prepareStatement(updateSql);
			System.out.println(" The sql statement is "+updateSql);

			// execute the update statement;
			noOfRecords = pstmtUpdate.executeUpdate();
			pstmtUpdate.close();
			pstmtUpdate=null;
			if (success && noOfRecords < 1 )
				success = false;

		} catch (java.sql.SQLException sqle) {
			success  = false;
			sysLogMgr.logMsg("Error in SecUsersHome.updateUser() SQL execution : " + updateSql + "\n" + sqle);
			sqle.printStackTrace();
		} catch (EbppException ebppe) {
			success  = false;
			sysLogMgr.logMsg("Error in SecUsersHome.updateUser() :\n" + ebppe);
			ebppe.printStackTrace();
		} finally {
			if (pstmtUpdate != null) {
				try {
					pstmtUpdate.close();
				} catch (SQLException e1) {
				}
			}

			if (connection !=null)
				dbh.freeDBConnection (connection);
		}

		return success;

	}

	
	
	/**Get user DB Id
	 * 
	 * 
	 * @param merchant
	 * @param logonUserRole
	 * @return ArrayList of UserDao
	 */
	public long getUserDBId (String merchant, String loginId) {
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
        long DBId = -1;
        String selectSql =  "SELECT ID FROM "  + Constants.DB_TBL_USER +
        " WHERE MER_NAME= ? AND LOGIN_ID= ? ";

        try {
            connection = dbh.getDBConnection();

            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, loginId);


            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                DBId = rs.getLong(1);
            }
			pstmtQuery.close();
            rs.close();
			pstmtQuery=null;
			rs=null;

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.getUserDBId() SQL execution : sql=" + selectSql + "\n " + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

	    return DBId;

	}

	
	
	/**Get user security information
	 * 
	 * @param merchant
	 * @param logonUserRole
	 * @return ArrayList of UserDao
	 */
	public UserDao getUserSecInfo (String merchant, String loginId) {
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
        UserDao user = new UserDao();

        String selectSql =  "SELECT A.ID, A.LOGIN_ID, A.NAME, A.AGENCY_CODE, C.AGENCY_DESC, A.LAST_LOGIN_TIMESTAMP, " +
        		"A.LAST_LOGIN_IP, A.ROLE_CODE, A.BAD_PWD_NO_ATTEMPTS, A.CREATED_BY, A.CREATED_TIMESTAMP, " +
        		"A.LAST_UPDATED_TIMESTAMP, A.LAST_UPDATED_BY, A.CREATE_STATUS, A.ENABLED, A.LOCKED, " +
                Constants.DB_TBL_USER + " A, " + Constants.DB_TBL_ACCESSROLE + " B " +
                " WHERE A.MER_NAME = ? AND A.LOGIN_ID= ? AND A.ROLE_CODE = B.ROLE_CODE " ;

        try {
            connection = dbh.getDBConnection();
            PropertyHome ph = PropertyHome.getInstance();

            //System.out.println(selectSql);

            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, loginId);

            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                user.setUserDBId(rs.getLong(1));
                user.setUserLogin(rs.getString(2));
                user.setUserName(rs.getString(3));
                user.setAgencyCode(rs.getString(4));
                user.setAgencyName(rs.getString(5));
                user.setLastLoginTimestamp(rs.getTimestamp(6));
                user.setLastLoginIp(rs.getString(7));
                user.setUserRole(rs.getString(8));
                user.setNoBadPwdAttempts(rs.getInt(9));
                user.setCreatedBy(rs.getString(10));
                user.setCreatedTimestamp(rs.getTimestamp(11));
                user.setLastUpdatedTimestamp(rs.getTimestamp(12));
                user.setLastUpdatedBy(rs.getString(13));
                user.setCreateStatus(rs.getString(14));
                user.setUserEnabled(rs.getString(15));

                user.setLockedFlag(rs.getString(16));
                user.setResetPwdFlag(rs.getString(17));
                user.setLastPwdChanged(rs.getTimestamp(18));
                user.setRemark(rs.getString(19));
                user.setRoleName(rs.getString(20));
                user.setCountryCode(rs.getString(21));

            }
            //System.out.println("***************************user.getCountryCode() is "+user.getCountryCode());
            pstmtQuery.close();
			rs.close();
			pstmtQuery=null;
			rs=null;

            // get email and contact No
            user.setEmail(ph.getProperty(Long.toString(user.getUserDBId()), user, UserDao.PROP_EMAIL));
            user.setHandPhoneNo(ph.getProperty(Long.toString(user.getUserDBId()), user, UserDao.PROP_HANDPHONE_NO));

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.getUserSecInfo() SQL execution : sql=" + selectSql + "\n " + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

	    return user;

	}

	
	
	/**
	 * Get list of users that the logonUserRole can see and users from child agencies only
	 * @param merchant
	 * @param logonUserRole
	 * @return ArrayList of UserDao
	 */
	//users = SecUsersHome.getInstance().getSearchUser(merchantId, strUIDGroupCode, strLogonUserAgencyCode, strLogonUserCountryCode);
	public UserDao getSearchUser (String merchant, String logonUserRoleId, String agencyCode, String countryCode,String searchLogonId, int maxBadPwdAttempts) {
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

        ArrayList users = new ArrayList();
        ArrayList roleIds = new ArrayList();
        ArrayList agencyIds = new ArrayList();
		UserDao user = new UserDao();
        String selectSql = "";
        ResultSet rs = null;
        //System.out.println("SecUserHome.getSearchUser Parameter merchant= " + merchant);
        //System.out.println("SecUserHome.getSearchUser Parameter logonUserRoleId= " + logonUserRoleId);
        //System.out.println("SecUserHome.getSearchUser Parameter agencyCode= " + agencyCode);
        //System.out.println("SecUserHome.getSearchUser Parameter countryCode= " + countryCode);
        //System.out.println("SecUserHome.getSearchUser Parameter searchLogonId= " + searchLogonId);
        //System.out.println("SecUserHome.getSearchUser Parameter maxBadPwdAttempts= " + maxBadPwdAttempts);
        //Step 1 : Get if it is valid user
		//Required Field User ID User Name Role Email Contact No. Enabled Locked Status
        selectSql = "SELECT A.ID, A.LOGIN_ID, A.NAME, A.AGENCY_CODE, A.ROLE_CODE, A.CREATE_STATUS, A.ENABLED, A.LOCKED, A.BAD_PWD_NO_ATTEMPTS, A.REMARKS, (SELECT ROLE_NAME FROM TBLACCESSROLE WHERE ROLE_CODE = a.ROLE_CODE) AS ROLE_NAME, A.COUNTRY_CODE FROM TBLUSER A WHERE A.MER_NAME = '" + merchant + "' AND A.LOGIN_ID = '" + searchLogonId + "'";
		System.out.println("SecUsersHome.java getSearchUser [selectSql_01_CheckUser]=" + selectSql);
        if (logonUserRoleId == null){
			logonUserRoleId = "";
		}
        try {
            connection = dbh.getDBConnection();

            // Get the role ids that logon user role can see
            pstmtQuery =  connection.prepareStatement(selectSql);
            rs = pstmtQuery.executeQuery();
			user = new UserDao();
            if (rs.next()) {
				user.setUserDBId(rs.getLong(1));
				user.setUserLogin(rs.getString(2));
				user.setUserName(rs.getString(3));
				user.setAgencyCode(rs.getString(4));
				user.setUserRole(rs.getString(5));
				user.setCreateStatus(rs.getString(6));
				user.setUserEnabled(rs.getString(7));

				if (!rs.getString(8).equals(Constants.DB_NO)) {
					user.setUserLocked(true);
				} else {
					int noPwdAttempts = rs.getInt(9);
					if (noPwdAttempts >= maxBadPwdAttempts) {
						user.setUserLocked(true);
					} else {
						user.setUserLocked(false);
					}
				}
				user.setRemark(rs.getString(10));
				user.setRoleName(rs.getString(11));
				user.setCountryCode(rs.getString(12));
				user.setIsAccessAllow(true);
            }else{
				user.setIsAccessAllow(false);
				user.setAccessReason("INVALID USER ID : USER NOT FOUND");
			}
            pstmtQuery.close();
            rs.close();
			pstmtQuery=null;
			rs=null;
			//System.out.println("SecUsersHome.java getSearchUser A1");
			if (user.getIsAccessAllow()){
				//Check if User is MSA. // MSA is allow to access all user.
				//System.out.println("SecUsersHome.java getSearchUser A2");
				if (logonUserRoleId.equals("MSA")){
					//System.out.println("SecUsersHome.java getSearchUser A3");
					user.setIsAccessAllow(true);
					user.setAccessReason("");
				}else{
					//System.out.println("SecUsersHome.java getSearchUser A4");
					//Search User country code == logon user country code
					//System.out.println("SecUsersHome.java getSearchUser A4_1 countryCode=[" + countryCode + "]");
					//System.out.println("SecUsersHome.java getSearchUser A4_1 user.getCountryCode()=[" + user.getCountryCode() + "]");
					if (user.getCountryCode().equals(countryCode)){
						//System.out.println("SecUsersHome.java getSearchUser A5");
						//Search if the search user group is child of the logon user group.
						selectSql = "SELECT MER_NAME,TREE_TYPE,PARENT_ID,CHILD_ID FROM TBLTREE WHERE MER_NAME = '"+ merchant +"' AND TREE_TYPE = '" + Constants.TREE_TYPE_ACCESS +"' AND PARENT_ID = '" + logonUserRoleId + "' AND CHILD_ID = '" + user.getUserRole() + "'";
						//System.out.println("SecUsersHome.java getSearchUser [selectSql_02_CheckRole]=" + selectSql);
			            pstmtQuery =  connection.prepareStatement(selectSql);
			            rs = pstmtQuery.executeQuery();
			            if (rs.next()) {
							user.setIsAccessAllow(true);
						}else{
							user.setIsAccessAllow(false);
							user.setAccessReason("UNAUTHORIZED : USER'S ROLE IS NOT ACCESSIBLE BY YOU");
						}
						//System.out.println("SecUsersHome.java getSearchUser A6");
						pstmtQuery.close();
						rs.close();
						pstmtQuery=null;
						rs=null;
						if (user.getIsAccessAllow()){
							//20101112: Update
							if((countryCode.equals(user.getCountryCode()))&&(agencyCode.equals(user.getAgencyCode()))){
								//User are from the same Agency so allow access by default.
							}else{
								//Search if the search user agency is child of the logon user agency.
								selectSql = "SELECT MER_NAME,TREE_TYPE,PARENT_ID,CHILD_ID FROM TBLTREE WHERE MER_NAME = '"+ merchant +"' AND TREE_TYPE = '" + Constants.TREE_TYPE_AGENCY +"' AND PARENT_ID = '" + agencyCode + "' AND CHILD_ID = '" + user.getAgencyCode() + "'";  //AND PARENT_COUNTRY_CODE = '" + countryCode + "' AND CHILD_COUNTRY_CODE = '" + user.getCountryCode() + "'" ;
								System.out.println("SecUsersHome.java getSearchUser [selectSql_03_CheckAgency]=" + selectSql);
								pstmtQuery =  connection.prepareStatement(selectSql);
								rs = pstmtQuery.executeQuery();
								//if (rs.next()) {
								//	user.setIsAccessAllow(true);
								//}else{
								//	user.setIsAccessAllow(false);
								//	user.setAccessReason("UNAUTHORIZED : USER'S AGENCY IS NOT ACCESSIBLE BY YOU");
								//}
								pstmtQuery.close();
								rs.close();
								pstmtQuery=null;
								rs=null;
							}
						}// end if access allow
						//System.out.println("SecUsersHome.java getSearchUser A7");
					}else{
						//System.out.println("SecUsersHome.java getSearchUser A8");
						user.setIsAccessAllow(false);
						user.setAccessReason("UNAUTHORIZED : USER IS FROM DIFFERENT COUNTRY");
					}//end if same country code

				}//end if role is MSA
			}//end if access of user allowed.
			//System.out.println("SecUsersHome.java getSearchUser user.getIsAccessAllow=" + user.getIsAccessAllow());
			//System.out.println("SecUsersHome.java getSearchUser user.getAccessReason=" + user.getAccessReason());
			//users.add(user);

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.getUsers() SQL execution : sql1=" + selectSql + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

	    return user;

	}

	public boolean userpasswordExpired(String merchant, String userId, String pwdExpiryDays){
		
		boolean expired = false;
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
		Timestamp usercreated = null;
		Timestamp lastpwdchanged = null;
		
     // check user created and last password changed
        String selectSql = "SELECT CREATED_TIMESTAMP, LAST_PWD_CHANGED FROM " + Constants.DB_TBL_USER + " WHERE MER_NAME = ? AND LOGIN_ID= ?  ";

        try {
            connection = dbh.getDBConnection();

            // check that the login_id to create does not exist
            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, userId);
			System.out.println("CPA.SecUsersHome.java userpasswordExpired selectSql="+selectSql);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            
            while (rs.next()) {
            	usercreated = rs.getTimestamp("CREATED_TIMESTAMP");
            	lastpwdchanged = rs.getTimestamp("LAST_PWD_CHANGED");
            }
            System.out.println("The timestamp for user created date is "+usercreated);
            System.out.println("The timestamp is for last password changed is "+lastpwdchanged);
            
            if (usercreated == null){
            	//set default value if null
            	usercreated = Timestamp.valueOf("2010-01-01 12:00:00.000");
            }
            String usercreatedstr = usercreated.toString();
            String lastpwdchangedstr = lastpwdchanged.toString();
            Date dateusercreated = new Date();
            Date datelastpwdchanged = new Date();
            DateFormat format = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss");
            
			try {
				dateusercreated = format.parse(usercreatedstr);
				datelastpwdchanged = format.parse(lastpwdchangedstr);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			//Date d1 = dateusercreated;
			Date d2 = lastpwdchanged;
			Date d3 = new Date();
			
			System.out.println("The input date now is "+d3);
			System.out.println("The input date pwd changed is "+d2);
			long diff = 0;
			long diffDays = 0;
			try {
				//d1 = format.parse(dateStart);
				//d2 = format.parse(dateStop);
	 
				//in milliseconds
				diff = d3.getTime() - d2.getTime();
				System.out.println("The difference in miliseconds is "+diff);
				long diffSeconds = diff / 1000 % 60;
				long diffMinutes = diff / (60 * 1000) % 60;
				long diffHours = diff / (60 * 60 * 1000) % 24;
				diffDays = diff / (24 * 60 * 60 * 1000);
	 
				System.out.println(diffDays + " days, ");
				System.out.println(diffHours + " hours, ");
				System.out.println(diffMinutes + " minutes, ");
				System.out.println(diffSeconds + " seconds.");
	 
			} catch (Exception e) {
				e.printStackTrace();
			}
			
			int diffday = Integer.parseInt(pwdExpiryDays);
			System.out.println("The password expiry days given is "+pwdExpiryDays);
			System.out.println("The difference in password days is "+diffDays);
			if (diffDays>diffday){
				expired = true;
			}
            if (diffDays<=diffday){
            	expired = false;
            }
            rs.close();

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.userpasswordExpired() SQL execution : " + selectSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

		return expired;
		
	}
	
	public boolean userAlreadyLogon ( String merchant, String userId, String ipaddress){
		
		boolean userloginstatus = false;
		Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
        String loginIp = "";
        String locked = "";
        
		//Check merchant, user ID and IP address if same return false else return true
        String selectSql = "SELECT LAST_LOGIN_IP, LOCKED FROM " + Constants.DB_TBL_USER + " WHERE MER_NAME = ? AND LOGIN_ID= ?  ";
        try {
            connection = dbh.getDBConnection();

            // check that the login_id to create does not exist
            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, userId);
			System.out.println("CPA.SecUsersHome.java userAlreadyLogon selectSql="+selectSql);

            // execute query
            ResultSet rs = pstmtQuery.executeQuery();
            while (rs.next()) {
            	loginIp = rs.getString("LAST_LOGIN_IP");
            	locked = rs.getString("LOCKED");
            }
            if (loginIp.equalsIgnoreCase(ipaddress)&&locked.equals("L")){
            	userloginstatus = true;
            }
            
        }
        catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.userAlreadyLogon() SQL execution : " + selectSql + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}
		
		return userloginstatus;
	}


	/**
	 * Get list of users that the logonUserRole can see and users from child agencies only
	 * @param merchant
	 * @param logonUserRole
	 * @return ArrayList of UserDao
	 */

	public ArrayList getInactiveUsers (String merchant) {
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();

        ArrayList users = new ArrayList();
        System.out.println("Start getInactiveUsers process..");
        
        String sbSelect = 
                "SELECT A.ID, A.LOGIN_ID, A.NAME, A.ROLE_CODE, A.CREATE_STATUS, A.ENABLED,  to_char(A.LAST_LOGIN_TIMESTAMP,'yyyy-MM-dd') as last_login FROM "
                        + Constants.DB_TBL_USER + " A WHERE A.MER_NAME = ?  AND A.ENABLED <> ? ";
        
        
    	
    	System.out.println("merchant is "+merchant);
    	System.out.println("SecUsersHome.getInactiveUsers sbSelect =[ "+sbSelect+"]");

        try {
            connection = dbh.getDBConnection();

            // Get the role ids that logon user role can see
            pstmtQuery =  connection.prepareStatement(sbSelect);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, "Y");
            
            ResultSet rs = pstmtQuery.executeQuery();            
            rs = pstmtQuery.executeQuery();
            while (rs.next()) {
				UserDao user = new UserDao();
				user.setUserDBId(rs.getLong(1));
    	System.out.println("login id is "+rs.getString(2));
				user.setUserLogin(rs.getString(2));
				user.setUserName(rs.getString(3));
				user.setUserRole(rs.getString(4));
				user.setCreateStatus(rs.getString(5));
				user.setUserEnabled(rs.getString(6));
				//user.setRemark(rs.getString(7));
				//user.setRoleName(rs.getString(8));
    	System.out.println("last login is "+rs.getString(7));
				user.setRemark(rs.getString(7));
				users.add(user);
            }
            rs.close();

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.getUsers() SQL execution : sql2=" +sbSelect + "\n" + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

	    return users;

	}	
}//end class